Only specified data from a string SQL-CodePudding

I have a review column in a table which has multiple strings formats like below

Example strings:

'05012:000000:  :0:00000000|00647:000000:  :0:00000000|00283:000000:  :0:00000000|'
'05012:000000:  :0:00000000|00025:000000:  :0:00000000|00647:000000:  :0:00000000|'
'05012:000000:  :0:00000000|02095:000000:  :0:00000000|00647:000000:  :0:00000000|'
'05012:000000:  :0:00000000|00647:000000:  :0:00000000|' 
'05081:023931:DF:9:20230111|00604:023931:XX:9:20230111|02470:023931:XX:9:20230111|00655:023931:XX:9:20230111|00464:023931:XX:9:20230111|02130:023931:XX:9:20230111|'

I need to get output like as below for the above strings.

The End Points are, [The End Points are "|" , ":" ]

Example strings: [output]

'05012,00647,00283'
'05012,00025,00647'
'05012,02095,00647'
'05012,00647', 
'05081,00604,02470,00655,00464,02130'

I have tried with REPLACE() with SUBTRING() string functions in SQL Server, but I couldn't get the above output.

CodePudding user response：

You can use STRING_SPLIT and STRING_AGG :-

https://dbfiddle.uk/db5FGFoF

 Create table Temp_Tbl (Comments varchar(500));

 INSERT INTO Temp_Tbl
 VALUES('05012:000000:  :0:00000000|00647:000000:  :0:00000000|00283:000000:  :0:00000000|'),
 ('05012:000000:  :0:00000000|00025:000000:  :0:00000000|00647:000000:  :0:00000000|'),
 ('05012:000000:  :0:00000000|02095:000000:  :0:00000000|00647:000000:  :0:00000000|')

 with cte as 
 (select Comments, value as Value1 from Temp_Tbl cross apply STRING_SPLIT(Comments,'|') 
 ), cte2 as
   (select Comments,Value1, value as Value2 from CTE cross apply STRING_SPLIT(Value1,':') 
 )
 select comments,STRING_AGG(Value2,',' ) from cte2  
 where Value2>0
 group by Comments

CodePudding user response：

select
    coalesce(nullif(substring(col,  1, 5)   ','), ','), '')  
    coalesce(nullif(substring(col, 28, 5)   ','), ','), '')  
    coalesce(nullif(substring(col, 55, 5)   ','), ','), '')  
    coalesce(nullif(substring(col, 82, 5)   ','), ','), '')      
    coalesce(nullif(substring(col,109, 5)   ','), ','), '')           
    coalesce(nullif(substring(col,136, 5)   ','), ','), '')
from T

select
    replace(replace(replace(
        substring(col,  1, 5)   ','  
        substring(col, 28, 5)   ','  
        substring(col, 55, 5)   ','  
        substring(col, 82, 5)   ','  
        substring(col,109, 5)   ','  
        substring(col,136, 5)   ','
    ',,,', ','), ',,', ','), ',,', ',')
from T