t-sql help

jfreeman2010
jfreeman2010 used Ask the Experts™
on
T-SQL:
In a stored procedure, have  input field varchar(200), see below table. Try to get output1 and output2 declare as  varchar(200).  Output1 will be distinct left of ‘:’ and Output2 will be distinct right ‘:’.  Both Output1 and Output2 will be use in a IN statement.

Thank you for helping!!

Input and output
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Duy PhamFreelance IT Consultant

Commented:
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

Open in new window

Author

Commented:
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!!
Duy PhamFreelance IT Consultant

Commented:
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

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi Duy,

How to put double quotes  to output2 because its string ?

Thank you,
Freelance IT Consultant
Commented:
You can modify the select @Output1 = ..., @Output2 = ... as below:
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)') + '"' + ','

Open in new window


But I really wonder why do you need to enclose in double quotes, because Output2 is actually a string. So I assume when parsing the data, you just need to split Output2 into a string array.
SharathData Engineer

Commented:
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"
*/

Open in new window

Author

Commented:
Hi Duy,

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

Author

Commented:
Hi Sharath,

Thank you for the help.  The input is a input field for stored procedure input, not in table.
SharathData Engineer
Commented:
create the proc first.
create procedure Parse_Input
@Input varchar(200),
@Output1 varchar(100) output,
@Output2 varchar(100) output
as 
;with CTE AS (
select Input,ltrim(SUBSTRING(Input, n, CHARINDEX(',', Input + ',',n) - n)) Parsed_Input
  from (select @Input Input) t1
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 @Output1 = STUFF(( SELECT DISTINCT ','+CONVERT(VARCHAR,left_part)
                FROM  CTE2 AS t2 WHERE t1.Input = t2.Input
                 FOR XML PATH('')), 1, 1, ''),
	  @Output2 = STUFF(( SELECT DISTINCT ','+CONVERT(VARCHAR,right_part)
                FROM  CTE2 AS t2 WHERE t1.Input = t2.Input
                 FOR XML PATH('')), 1, 1, '') 
  from CTE2 t1
return

Open in new window

then call the proc in the code.
declare @Input varchar(200) = '136:HMO,365:HMO,136:PPO,365:UNINSURED'
declare @Output1 varchar(100), @Output2 varchar(100)
execute dbo.Parse_Input @Input,@Output1=@Output1 output, @Output2=@Output2 output
select @Input Input, @Output1 Output1, @output2 Output2
/*
Input	Output1	Output2
136:HMO,365:HMO,136:PPO,365:UNINSURED	136,365	"HMO","PPO","UNINSURED"
*/

Open in new window

Author

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

Author

Commented:
thank you very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial