t-sql help

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
jfreeman2010Asked:
Who is Participating?
 
Duy PhamConnect With a Mentor Freelance IT ConsultantCommented:
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.
0
 
Duy PhamFreelance IT ConsultantCommented:
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

0
 
jfreeman2010Author 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!!
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Duy PhamFreelance IT ConsultantCommented:
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

0
 
jfreeman2010Author Commented:
Hi Duy,

How to put double quotes  to output2 because its string ?

Thank you,
0
 
SharathData EngineerCommented:
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

0
 
jfreeman2010Author Commented:
Hi Duy,

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

Thank you for the help.  The input is a input field for stored procedure input, not in table.
0
 
SharathConnect With a Mentor Data EngineerCommented:
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

0
 
jfreeman2010Author Commented:
Thank you very much for Duy and Sharath. You both very help.  Thank you
0
 
jfreeman2010Author Commented:
thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.