Solved

t-sql help

Posted on 2016-09-12
11
46 Views
Last Modified: 2016-09-12
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
0
Comment
Question by:jfreeman2010
  • 6
  • 3
  • 2
11 Comments
 
LVL 10

Expert Comment

by:Duy Pham
ID: 41794396
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
 

Author Comment

by:jfreeman2010
ID: 41794463
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
 
LVL 10

Expert Comment

by:Duy Pham
ID: 41794556
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:jfreeman2010
ID: 41794601
Hi Duy,

How to put double quotes  to output2 because its string ?

Thank you,
0
 
LVL 10

Accepted Solution

by:
Duy Pham earned 250 total points
ID: 41794712
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
 
LVL 40

Expert Comment

by:Sharath
ID: 41794762
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
 

Author Comment

by:jfreeman2010
ID: 41794786
Hi Duy,

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

Author Comment

by:jfreeman2010
ID: 41794799
Hi Sharath,

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

Assisted Solution

by:Sharath
Sharath earned 250 total points
ID: 41794838
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
 

Author Comment

by:jfreeman2010
ID: 41794887
Thank you very much for Duy and Sharath. You both very help.  Thank you
0
 

Author Closing Comment

by:jfreeman2010
ID: 41794892
thank you very much
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now