Solved

t-sql help

Posted on 2016-09-12
11
37 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:jfreeman2010
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
Hi Duy,

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

Author Comment

by:jfreeman2010
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you very much for Duy and Sharath. You both very help.  Thank you
0
 

Author Closing Comment

by:jfreeman2010
Comment Utility
thank you very much
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

771 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

12 Experts available now in Live!

Get 1:1 Help Now