CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
JVGBI
JVGBI

Reputation: 575

SQL Query: Remove multiple characters strings in a long varchar(max) column

I have the following string in a varchar(max) column:

PREV - FirstName: John / LAST - FirstName: Johan; PREV- LastName: Crescot / LAST - LastName: Crescott;

After every semicolon can come endless amounts PREV values and LAST value mutations depending on the amount of changes done in the source system.

I need to write a query that returns ONLY the PREV values. In case of the string above, the desired result would be:

FirstName: John; LastName: Crescot

All the slash (/) delimiters and dashes need to be removed as well, as you can see in the required result.

Could anyone help me with this? Thank you all!

Upvotes: 2

Views: 624

Answers (2)

Kapil
Kapil

Reputation: 987

 create table #temp(val varchar(max))

 Insert into #temp values('PREV - FirstName: John / LAST - FirstName: Johan; PREV - LastName: Crescot / LAST - LastName')

Select stuff(
 (SELECT ';'+ 
  Replace(stuff(Tbl.Col.value('./text()[1]','varchar(50)'),charindex('/',Tbl.Col.value('./text()[1]','varchar(50)')),len(Tbl.Col.value('./text()[1]','varchar(50)')),''),'PREV -','')as ColName
FROM 
(Select cast('<a>'+ replace((SELECT val As [*] FOR XML PATH('')), ';', '</a><a>') + '</a>' as xml)as t 
 from #temp) tl
Cross apply 
tl.t.nodes('/a') AS Tbl(Col) for xml path(''),type).value('.','NVARCHAR(MAX)'),1,2,'')

This method does not need any additional UDF. Breaking down the above query for easy understanding: 1. Convert one row of string to multiple rows based on semicolon ';'

SELECT 
 Tbl.Col.value('./text()[1]','varchar(50)')
FROM 
(Select cast('<a>'+ replace((SELECT val As [*] FOR XML PATH('')), ';', '</a><a>') + '</a>' as xml)as t 
  from #temp) tl
Cross apply 
tl.t.nodes('/a') AS Tbl(Col)

2.Over the above extracted value ,use replace and stuff commands to remove unnecessary characters

        SELECT 
   Replace(stuff(Tbl.Col.value('./text()[1]','varchar(50)'),charindex('/',Tbl.Col.value('./text()[1]','varchar(50)')),len(Tbl.Col.value('./text()[1]','varchar(50)')),''),'PREV -','')as ColName
FROM 
(Select cast('<a>'+ replace((SELECT val As [*] FOR XML PATH('')), ';', '</a><a>') + '</a>' as xml)as t 
  from #temp) tl
Cross apply 
tl.t.nodes('/a') AS Tbl(Col)

3. Use stuff and xml path to make the multiple rows back to a single row separated by semicolons as required

     Select stuff(
(SELECT ';'+ 
 Replace(stuff(Tbl.Col.value('./text()[1]','varchar(50)'),charindex('/',Tbl.Col.value('./text()[1]','varchar(50)')),len(Tbl.Col.value('./text()[1]','varchar(50)')),''),'PREV -','')as food_Name
FROM 
(Select cast('<a>'+ replace((SELECT val As [*] FOR XML PATH('')), ';', '</a><a>') + '</a>' as xml)as t 
  from #temp) tl
 Cross apply 
tl.t.nodes('/a') AS Tbl(Col) for xml path(''),type).value('.','NVARCHAR(MAX)'),1,2,'')

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81940

If open to a UDF, consider the following.

Tired of extracting strings (charindindex, patindex, left, right...), I modified a parse function to accept two non-like parameters. In this case a 'PREV' and '/'

Example

Declare @YourTable table (ID int,SomeCol varchar(max))
Insert Into @YourTable values
(1,'PREV - FirstName: John / LAST - FirstName: Johan; PREV- LastName: Crescot / LAST - LastName: Crescott;')

Select A.ID
      ,B.NewVal 
 From  @YourTable A
 Cross Apply (
                Select NewVal = Stuff((Select '; '+ltrim(rtrim(replace(RetVal,'-','')))
                                        From  [dbo].[udf-Str-Extract](A.SomeCol,'PREV','/') 
                                         For  XML Path ('')),1,2,'') 
             ) B

Returns

ID  NewVal
1   FirstName: John; LastName: Crescot

The UDF if Interested

CREATE FUNCTION [dbo].[udf-Str-Extract] (@String varchar(max),@Delimiter1 varchar(100),@Delimiter2 varchar(100))
Returns Table 
As
Return (  

with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
       cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 N1,cte1 N2,cte1 N3,cte1 N4,cte1 N5,cte1 N6) A ),
       cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter1) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter1)) = @Delimiter1),
       cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter1,@String,s.N),0)-S.N,8000) From cte3 S)

Select RetSeq = Row_Number() over (Order By N)
      ,RetPos = N
      ,RetVal = left(RetVal,charindex(@Delimiter2,RetVal)-1) 
 From  (
        Select *,RetVal = Substring(@String, N, L) 
         From  cte4
       ) A
 Where charindex(@Delimiter2,RetVal)>1

)
/*
Max Length of String 1MM characters

Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[udf-Str-Extract] (@String,'[[',']]')
*/

Upvotes: 1

Related Questions