CODEWITHSUNDEEP
sql-serversplit
Edinei Raduvanski
Edinei Raduvanski

Reputation: 55

split a string by commas (,) SQL SERVER

I would like to split a string by commas (,) or pipe (|) to each character in SQL SERVER. Example 'APPLE'. Expected result: 'A|P|P|L|E'. Preferably without creating function.

Upvotes: 0

Views: 345

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44326

DECLARE @txt varchar(50) ='APPLE'

;WITH cte(x) as
(
  SELECT top (len(@txt)) ';' 
    + substring(@txt, row_number() over (order by (select 1)), 1)
  FROM master..spt_values x1
  cross join
  master..spt_values x2
  FOR XML PATH('')
)
SELECT stuff(x, 1, 1, '')
FROM CTE

Result

A;P;P;L;E

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can do it with CTE:

DECLARE @s NVARCHAR(MAX) = 'APPLE'
DECLARE @result NVARCHAR(MAX)

;WITH cte(N, S) AS 
(
    SELECT 1 AS N, SUBSTRING(@s, 1, 1)
    UNION ALL
    SELECT N + 1, SUBSTRING(@s, N + 1, 1)
    FROM cte
    WHERE N < LEN(@s)
) 
SELECT @result = COALESCE(@result + '|', '') + S FROM cte
SELECT @result

Output:

A|P|P|L|E

Or even shorter version:

DECLARE @s NVARCHAR(MAX) = 'APPLE'

;WITH cte(N, S, D) AS 
(
    SELECT 1 AS N, SUBSTRING(@s, 1, 1), D = SUBSTRING(@s, 1, 1)
    UNION ALL
    SELECT N + 1, SUBSTRING(@s, N + 1, 1), D = D + '|' + SUBSTRING(@s, N + 1, 1)
    FROM cte
    WHERE N < LEN(@s)
) 
SELECT TOP 1 D FROM cte
ORDER BY N DESC

Upvotes: 2

Jeremy
Jeremy

Reputation: 4838

You could use a concept like the "Tally Table String Splitter" to achieve what you want.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Upvotes: 1

Related Questions