How to split a string with special text from SQL column?

Transact-SQL
, , , ,
1
  • I have a column with below data which contains multiple set of tags that starting with "tl:collection:"
  • How can i split the data that contains tl:collection: into separate column.

Sample Column Data:

tl:Namespace:ingress, tl:ImageName:text1/54445t6,
tl:collection:stg-r2-pksa, tl:cmdb:AppID:1070242777, tl:collection:Privileged_Policy, tl:collection:prd-lv-v1-pksa,
tl:Namespace:ingress-udp, tl:collection:prd-atl-a1-pks, tl:collection:dev-r2-pksa,
tl:ImageName:text1/54445t6, tl:collection:nre-npdp1-pksa

Expected output:

SNo | COLLECTION

1 | stg-r2-pksa
2 | Privileged_Policy
3 | prd-lv-v1-pksa
4 | prd-atl-a1-pks
5 | dev-r2-pksa
6 | nre-npdp1-pksa

2

hi

hope this helps

drop table if exists #data

create sample data script

create table #data(Text varchar(500))
insert into #data select 'tl:Namespace:ingress, tl:ImageName:text1/54445t6,tl:collection:stg-r2-pksa, tl:cmdb:AppID:1070242777, tl:collection:Privileged_Policy, tl:collection:prd-lv-v1-pksa,tl:Namespace:ingress-udp, tl:collection:prd-atl-a1-pks, tl:collection:dev-r2-pksa,tl:ImageName:text1/54445t6, tl:collection:nre-npdp1-pksa'
select * from #data

SELECT
    row_number() over(order by (select null))  as SNo 
	, replace(value,'tl:collection:','')  as COLLECTION
FROM
   #data 
     cross apply 
   string_split(text,',') 
WHERE
  value like '%tl:collection:%'

image
image1061×287 7.37 KB

3

@harishgg1 Thank you Very much! if possible can you help me one more process. collection column change to horizontal result.

4

hi

is this how you want ? or seperate columns

image
image855×224 4.22 KB

5

like this!

image
image1365×102 6.04 KB

6

hi

hope this helps

; with cte_rn as 
(
SELECT ROW_NUMBER() over( order by value) as rn , value FROM  #data cross apply string_split(text,',') WHERE value like '%tl:collection:%' OR value like '%AppID%'
)
select 
  max(case when rn = 1 then replace(value,'tl:cmdb:AppID:','') end)  as AppID 
  ,max(case when rn = 2 then replace(value,'tl:collection:','') end)  as Collection1 
  ,max(case when rn = 3 then replace(value,'tl:collection:','') end)  as Collection2
  ,max(case when rn = 4 then replace(value,'tl:collection:','') end)  as Collection3 
  ,max(case when rn = 5 then replace(value,'tl:collection:','') end)  as Collection4 
  ,max(case when rn = 6 then replace(value,'tl:collection:','') end)  as Collection5 
  ,max(case when rn = 7 then replace(value,'tl:collection:','') end)  as Collection6 
from cte_rn

image
image1163×257 7.03 KB

1 Like
7

yes, It works, Thank you so much @harishgg1

8

Be careful here, folks. There is no guarantee of order using the STRING_SPLIT() function in versions prior to SQL Server 2022. It will "usually" appear to work but there are no guarantees.

@harishgg1 - You know what the alternative is.