Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to make a split function for this parameter? Expand / Collapse
Author
Message
Posted Thursday, October 18, 2012 12:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 212, Visits: 1,388
here iam using a parameter

declare @Questions varchar(MAX)='1-2|32,42-41|44'

this should be splited in to three column


declare @Questions varchar(MAX)='1-32,42-41'
;WITH CTE1 AS(
SELECT id, LEFT(val, CHARINDEX('-', val)-1) AS QuestionId, SUBSTRING(val, CHARINDEX('-', val)+1, 100) AS [IndexNumber]
FROM [dbo].[FN_SplitData](@Questions,',')
)



select QuestionId,[IndexNumber] from CTE1


in which i receviwed output like this
QuestionId IndexNumber
1 32
42 41

using this split function
ALTER FUNCTION [dbo].[FN_SplitData](
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

INSERT INTO @t(val)
SELECT r.value('.','varchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END

i just tried like this

declare @Questions varchar(MAX)='1-32|26,42-41|32'
;WITH CTE1 AS(
SELECT id, LEFT(val, CHARINDEX('-', val)-1) AS QuestionId, SUBSTRING(val, CHARINDEX('-', val)+1, 100) AS [IndexNumber]
, SUBSTRING(val, CHARINDEX('|', val)+1, 100) AS Numbers
FROM [dbo].[FN_SplitData](@Questions,',')
)

select QuestionId,[IndexNumber],Numbers from CTE1


which provieded output like this

QuestionId IndexNumber Numbers
1 32|26 26
42 41|32 32
but iam trying output like this

QuestionId IndexNumber Numbers
1 32 26
42 41 32



can any one plz try to help me to solve this....
Post #1374163
Posted Thursday, October 18, 2012 4:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 8, 2014 7:35 AM
Points: 888, Visits: 672
try below code....

declare @Questions varchar(MAX)='1-32|26,42-41|32'
--select * FROM [dbo].split(@Questions)

SELECT LEFT(val, CHARINDEX('-', val)-1) AS QuestionId
, substring(val,(CHARINDEX('-', val)+1),(CHARINDEX('|', substring(val,(CHARINDEX('-', val)+1),len(val)))-1)) AS [IndexNumber]
, SUBSTRING(val, CHARINDEX('|', val)+1, 100) AS Numbers
from FN_SplitData(@Questions,',')
Post #1374264
Posted Friday, October 19, 2012 12:49 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721

DECLARE
@S VARCHAR(MAX)
,@Split1 CHAR(1)
,@Split2 CHAR(1)
,@Split3 CHAR(1)
,@X XML


/* User input */

SELECT
@S = '1-32|26,42-41|32'
,@Split1 = ','
,@Split2 = '-'
,@Split3 = '|'


/* Split the string and convert to XML */

SET @S = '<Question><QuestionID>'+REPLACE(@S,@Split1,'</Value></Question><Question><QuestionID>')
SET @S = REPLACE(@S,@Split2,'</QuestionID><IndexNum>')
SET @S = REPLACE(@S,@Split3,'</IndexNum><Value>')+'</Value></Question>'
SELECT @X = CONVERT(XML,'<root>' + @S + '</root>')


/* You could stop here and just display the data as XML */
SELECT @X


/* Display the results in a table */

DECLARE @XML_Temp TABLE (XML_Content XML)

INSERT INTO @XML_Temp VALUES (@X)
SELECT
Node.value('(QuestionID)[1]', 'varchar(50)') AS 'QuestionID'
,Node.value('(IndexNum)[1]', 'varchar(50)') AS 'IndexNum'
,Node.value('(Value)[1]', 'varchar(50)') AS 'Value'
FROM
@XML_Temp
CROSS APPLY
@X.nodes('root/Question/.') AS Content(Node)

/* Output:
QuestionId IndexNumber Value
1 32 26
42 41 32
*/


Post #1375002
Posted Friday, October 19, 2012 4:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 35,808, Visits: 32,484
subbareddy542 (10/18/2012)
try below code....

declare @Questions varchar(MAX)='1-32|26,42-41|32'
--select * FROM [dbo].split(@Questions)

SELECT LEFT(val, CHARINDEX('-', val)-1) AS QuestionId
, substring(val,(CHARINDEX('-', val)+1),(CHARINDEX('|', substring(val,(CHARINDEX('-', val)+1),len(val)))-1)) AS [IndexNumber]
, SUBSTRING(val, CHARINDEX('|', val)+1, 100) AS Numbers
from FN_SplitData(@Questions,',')


Not very useful unless one has a copy of "FN_SplitData". Please post it. Thanks.

Edit: Apologies and never mind... I see it further above in this thread.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1375053
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse