how to make a split function for this parameter?

• Sivaganesh Tamilvendhan

  Hall of Fame

  Points: 3498

  More actions

  October 18, 2012 at 12:36 am

  #269705

  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|2626

  42 41|3232

  but iam trying output like this

  QuestionId IndexNumberNumbers

  1 32 26

  42 41 32

  can any one plz try to help me to solve this....

• subbu1

  Hall of Fame

  Points: 3281

  More actions

  October 18, 2012 at 4:45 am

  #1550395

  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,',')

• Steven Willis

  SSCrazy Eights

  Points: 9893

  More actions

  October 19, 2012 at 12:49 pm

  #1551025

  DECLARE

  @s-2 VARCHAR(MAX)

  ,@Split1 CHAR(1)

  ,@Split2 CHAR(1)

  ,@Split3 CHAR(1)

  ,@X XML

  /* User input */

  SELECT

  @s-2 = '1-32|26,42-41|32'

  ,@Split1 = ','

  ,@Split2 = '-'

  ,@Split3 = '|'

  /* Split the string and convert to XML */

  SET @s-2 = '<Question><QuestionID>'+REPLACE(@S,@Split1,'</Value></Question><Question><QuestionID>')

  SET @s-2 = REPLACE(@S,@Split2,'</QuestionID><IndexNum>')

  SET @s-2 = REPLACE(@S,@Split3,'</IndexNum><Value>')+'</Value></Question>'

  SELECT @X = CONVERT(XML,'<root>' + @s-2 + '</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

  */

• Jeff Moden

  SSC Guru

  Points: 1003857

  More actions

  October 19, 2012 at 4:04 pm

  #1551070

  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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)