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

Question

• I have a column with below data which contains multiple set of tags that starting with "tl:e1:"
• How can i split the data that contains tl:e1: into separate column.

Answer 1

tl:Namespace:iqqngress, tl:ImageName:text1/54445t6, tl:collection:stg-r2-pksa, tl:cmdb:AppID:6100762427, 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

Answer 2

Hi @RAVITEJASRIDASYAM-3196

You can try this query.

create table test(col varchar(max));
insert into test values('tl:Namespace:iqqngress, tl:ImageName:text1/54445t6, tl:collection:stg-r2-pksa, tl:cmdb:AppID:6100762427, 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')


;with T1 as(
  select value from test cross apply string_split(col,',')
),T2 as(
  select Ltrim(value) as [COLLECTION] from T1
),T3 as(
  select * from T2 where [COLLECTION] like 'tl:collection:%'
),T4 as(
  select trim('tl:collection:' from [COLLECTION]) as [COLLECTION] from T3)
select row_number()over(order by [COLLECTION]) as Rno,[COLLECTION] from T4;

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.