-3

I need to split given string into a single column.

Example:

Given String: 

'A,B,C,D,E,F'

Expected Result:

ColumnA
--------
A
B
C
D
E
F
Sarfaraz Makandar
  • 5,933
  • 16
  • 59
  • 84
  • http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns may help – artm Oct 17 '14 at 07:32
  • This might help you.. http://www.codeproject.com/Tips/798671/Split-string-into-Rows-Columns-using-Delimiters – Sachin Oct 17 '14 at 07:36
  • possible duplicate of [T-SQL: Opposite to string concatenation - how to split string into multiple records](http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco) – bummi Oct 17 '14 at 07:36

2 Answers2

2

Use custom split() function

CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (colA nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)

SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN

SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
IF @INDEX !=0

SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING

INSERT INTO @Results(colA) VALUES(@SLICE)

SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)

IF LEN(@STRING) = 0 BREAK
END
RETURN
END

Then run the query like below.

SELECT colA FROM [dbo].[Split] ('A,B,C,D,E,F', ',')


Demo

Ullas
  • 11,450
  • 4
  • 33
  • 50
0
declare @commavalue varchar(50)='A,B,C,D,E,F'
select q2.value from
(SELECT cast('<x>'+replace(@commavalue,',','</x><x>')+'</x>' as xml) as Data)q1 CROSS APPLY
(SELECT x.value('.','varchar(100)') as value FROM Data.nodes('x') as f(x))q2
mohan111
  • 8,633
  • 4
  • 28
  • 55