t-sql help

You could take below sample stored-procedure as the reference for your own:

create proc spSampleParser
	@strInput VARCHAR(200)
as
	-- format as XML
	declare @oXML XML
	select @oXML = CAST(('<I><O1>' + REPLACE(REPLACE(@strInput, ',', '</O2></I><I><O1>'),':','</O1><O2>') + '</O2></I>') AS XML)
	
	-- select output1, output2
	declare @Output1 varchar(200), @Output2 varchar(200)
	select	@Output1 = ISNULL((SELECT o1node.value('.', 'varchar(200)') + ',' FROM @oXML.nodes('/I/O1') AS oXML(o1node) FOR XML PATH('')), ''),
			@Output2 = ISNULL((SELECT o2node.value('.', 'varchar(200)') + ',' FROM @oXML.nodes('/I/O2') AS oXML(o2node) FOR XML PATH('')), '')

	-- remove trailing comma
	select	Output1 = SUBSTRING(@Output1, 1, LEN(@Output1) - 1), Output2 = SUBSTRING(@Output2, 1, LEN(@Output2) - 1)
return

Select allOpen in new window

Hi Duy Pham,

Thank you very much for your help.  I am lost in your code (I did not know any XML code), but the code/result works 90%.

Here are my re-write and I need the output1 and output2 to distinct the output which means output1 should be looks like: '136, 365' and output2 should be '"HMO", "PPO", "UNINSURED"':
DECLARE @ServiceID                  VARCHAR(200);
declare @oXML XML;
declare @Output1 varchar(200), @Output2 varchar(200);

--set @ServiceID      = '2:INSURED,281:INSURED';
set @ServiceID      = '136:HMO,365:HMO,136:PPO,365:UNINSURED';


select @oXML = CAST(('<I><O1>' + REPLACE(REPLACE(@ServiceID, ',', '</O2></I><I><O1>'),':','</O1><O2>') + '</O2></I>') AS XML)
-- select output1, output2
      
select      @Output1 = ISNULL((SELECT o1node.value('.', 'varchar(200)') + ',' FROM @oXML.nodes('/I/O1') AS oXML(o1node) FOR XML PATH('')), ''),
            @Output2 = ISNULL((SELECT o2node.value('.', 'varchar(200)') + ',' FROM @oXML.nodes('/I/O2') AS oXML(o2node) FOR XML PATH('')), '')

select      Output1 = SUBSTRING(@Output1, 1, LEN(@Output1) - 1),
            Output2 = SUBSTRING(@Output2, 1, LEN(@Output2) - 1)

Thank you very much for your help!!

You just need to add DISTINCT keyword before o1node.value and o2node.value as below:

create proc spSampleParser
	@strInput VARCHAR(200)
as
	-- format as XML
	declare @oXML XML
	select @oXML = CAST(('<I><O1>' + REPLACE(REPLACE(@strInput, ',', '</O2></I><I><O1>'),':','</O1><O2>') + '</O2></I>') AS XML)
	
	-- select output1, output2
	declare @Output1 varchar(200), @Output2 varchar(200)
	select	@Output1 = ISNULL((SELECT DISTINCT o1node.value('.', 'varchar(200)') + ',' FROM @oXML.nodes('/I/O1') AS oXML(o1node) FOR XML PATH('')), ''),
		@Output2 = ISNULL((SELECT DISTINCT o2node.value('.', 'varchar(200)') + ',' FROM @oXML.nodes('/I/O2') AS oXML(o2node) FOR XML PATH('')), '')

	-- remove trailing comma
	select	Output1 = SUBSTRING(@Output1, 1, LEN(@Output1) - 1), Output2 = SUBSTRING(@Output2, 1, LEN(@Output2) - 1)
return

Select allOpen in new window

Hi Duy,

How to put double quotes  to output2 because its string ?

Thank you,

declare @table table(Input varchar(200))
insert @table values ('-1:Insured'),('2:Insured,281:Insured'),('2:Insured'),('281:Insured'),('136:HMO,365:HMO,136:PPO,365:UNINSURED'),
                     ('365:HMO,136:PPO,365:UNINSURED')

;with CTE AS (
select Input,ltrim(SUBSTRING(Input, n, CHARINDEX(',', Input + ',',n) - n)) Parsed_Input
  from @table
cross join (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
WHERE SUBSTRING(',' + Input, n, 1) = ','
   AND n < LEN(Input) + 1),
   CTE2 AS (
select *,left(Parsed_Input,charindex(':',Parsed_Input)-1) left_part,
      '"'+substring(Parsed_Input,charindex(':',Parsed_Input)+1,len(Parsed_Input))+'"' right_part
  from CTE)
SELECT distinct Input,
       STUFF(( SELECT DISTINCT ','+CONVERT(VARCHAR,left_part)
                FROM  CTE2 AS t2 WHERE t1.Input = t2.Input
                 FOR XML PATH('')), 1, 1, '') Output1,
	  STUFF(( SELECT DISTINCT ','+CONVERT(VARCHAR,right_part)
                FROM  CTE2 AS t2 WHERE t1.Input = t2.Input
                 FOR XML PATH('')), 1, 1, '') Output2
  from CTE2 t1
/*
Input	Output1	Output2
-1:Insured	-1	"Insured"
136:HMO,365:HMO,136:PPO,365:UNINSURED	136,365	"HMO","PPO","UNINSURED"
2:Insured	2	"Insured"
2:Insured,281:Insured	2,281	"Insured"
281:Insured	281	"Insured"
365:HMO,136:PPO,365:UNINSURED	136,365	"HMO","PPO","UNINSURED"
*/

Select allOpen in new window

Hi Duy,

The double quotes in output2 is for this string to use like:
x IN ("HMO", "PPO",...)

Hi Sharath,

Thank you for the help.  The input is a input field for stored procedure input, not in table.

Thank you very much for Duy and Sharath. You both very help.  Thank you

thank you very much