Split string into multiple columns

• Sagar Sawant

  SSC Eights!

  Points: 853

  More actions

  November 22, 2011 at 10:12 pm

  #252331

  Hi guys,

  I have a string "1|HK$ 500 (Gift voucher)|500|2,1|HK$ 1000 (Gift voucher)|1000|2"

  I want to split comma(,) first. So two records will show.

  So it will be two separate records like,

  1|HK$ 500 (Gift voucher)|500|2

  1|HK$ 1000 (Gift voucher)|1000|2

  Now I want result like as below i.e. into multiple columns i.e in 4 columns. So it will be,

  1 HK$ 500 (Gift voucher) 500 2

  1 HK$ 500 (Gift voucher) 1000 2

  Any help would be greatly appreciated.

• Sagar Sawant

  SSC Eights!

  Points: 853

  More actions

  November 23, 2011 at 12:04 am

  #1411962

  Guys,

  Below one worked for me.

  -- Itzik's VATN

  CREATE FUNCTION dbo.fn_nums(@n AS bigint) RETURNS TABLE AS

  RETURN

  WITH

  L0 AS(SELECT 1 AS c UNION ALL SELECT 1),

  L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),

  L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),

  L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),

  L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),

  L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),

  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)

  SELECT TOP (@n) n AS Number FROM Nums WHERE n <= @n ORDER BY n;

  GO

  -- Erland's split function

  CREATE FUNCTION dbo.inline_split_me(@param nvarchar(MAX))

  RETURNS TABLE AS

  RETURN(

  SELECT

  ROW_NUMBER() OVER(ORDER BY Number) AS pos,

  ltrim(

  rtrim(

  convert(nvarchar(4000), substring(@param, Number,

  charindex(N',' COLLATE Slovenian_BIN2, @param + convert(nvarchar(MAX), N','), Number) - Number)

  ))) AS Value

  FROM dbo.fn_nums(convert(int, len(@param)))

  WHERE substring(convert(nvarchar(MAX), N',') + @param, Number, 1) = N',' COLLATE Slovenian_BIN2

  )

  GO

  DECLARE @T TABLE (

  id int NOT NULL IDENTITY(1, 1) UNIQUE,

  data varchar(max)

  );

  insert @T values('DEF,KHL,MNO');

  insert @T values('DEF,KHL,MNO,LKJ,MNJ,BKS');

  SELECT

  *

  FROM

  @T AS T

  OUTER APPLY

  dbo.inline_split_me(T.data) AS S

  PIVOT

  (

  MAX(Value)

  FOR pos IN ([1], [2], [3], [4], [5], [6])

  ) AS P;

  GO

  DROP FUNCTION dbo.inline_split_me, dbo.fn_nums;

  GO

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  November 23, 2011 at 1:42 am

  #1411993

  Jeff Moden wrote an article about splitting delimited stings here[/url].

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---