Parse string in SQL Server 2016

Question

I have a semi colon delimited string variable in my stored procedure with contents shown in quotes below with a phrase and name separated by two dashes "--".

String Variable below.

Outside Hire--Hire #01; Reassignment - External--Hire #15; Reassignment - External--Hire #21; Outside Hire--Hire #03; Internal Reassignment--John Pen; Outside Hire--Kara Emma

I would like to parse it out such that it shows the names associated with each phrase in brackets, separated by a comma and the number of times the phrase occurs. Example of result is displayed below.

Result

3 Outside Hire(Hire #01, Hire #03, Kara Emma); 2 Reassignment - External(Hire #15, Hire #21); 1 Internal Reassignment(John Pen)

The real problem looks like you are storing delimited data in your RDBMS. Seems you really need tom fix the design, what ever it is. — Thom A
– Thom A ♦, Commented Dec 17, 2020 at 21:49
Please read this for some tips on improving your question. You're missing a few important items, e.g showing what you tried and including a specific programming question. You don't have any question. — HABO
– HABO, Commented Dec 17, 2020 at 21:57
Hi Larnu. I'll confess the contents of the string variable with semi colons as a delimiter isn't fancy but that is what I could craft after massaging data retrieved from a combination of tables. Semi colons are then stored in variable tables where it is queried further and then displayed. — Ropeh
– Ropeh, Commented Dec 18, 2020 at 15:55

John Cappelletti · Accepted Answer · 2020-12-17 22:51:43Z

A little ugly, but perhaps this will help

Example

Declare @S varchar(max) = 'Outside Hire--Hire #01; Reassignment - External--Hire #15; Reassignment - External--Hire #21; Outside Hire--Hire #03; Internal Reassignment--John Pen; Outside Hire--Kara Emma'

;with cte as (
Select Cnt=sum(1) over (Partition By Pos1)
      ,Pos1
      ,Pos2
 From  string_split(@S,';') A
 Cross Apply (
                Select Pos1 = trim(JSON_VALUE(S,'$[0]'))
                      ,Pos2 = trim(JSON_VALUE(S,'$[1]'))
                 From  ( values ( '["'+replace(replace(Value,'"','\"'),'--','","')+'"]' ) ) A(S)
             ) B
),  cte2 as (
Select Distinct TmpStr = concat(A.cnt,' ',Pos1,'(',Stuff((Select ', ' +Pos2 From cte Where Pos1=A.Pos1 For XML Path ('')),1,2,'') ,')') 
 From  cte A
)
Select NewStr = Stuff((Select '; '+TmpStr From cte2 order by TmpStr Desc For XML Path ('')),1,2,'')

Returns

NewStr
3 Outside Hire(Hire #01, Hire #03, Kara Emma); 2 Reassignment - External(Hire #15, Hire #21); 1 Internal Reassignment(John Pen)

Collectives™ on Stack Overflow

Parse string in SQL Server 2016

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related