split a string by commas (,) SQL SERVER

Question

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.

Use sql for retrieving and storing data to a database. Use a real programming language for manipulating strings. — Mike Nakis, Feb 04 '15 at 13:55
If you would like to use function there is already one created by @vzczc in this topic: http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x — Rafał Czabaj, Feb 04 '15 at 14:03
isn't there a sqlServer function: RegexReplace ? I'm not terribly familiar with sqlServer, however, in Oracle: select regexp_replace('APPLE','(.)','\1|') r from dual works fine. It should be just a matter of using sqlServer's function instead ? If so, that should perform better than a function, wouldn't it? — Ditto, Feb 04 '15 at 14:08
@MikeNakis sometimes there is no "real language" front end to do this stuff with. — Jeremy, Feb 04 '15 at 14:16
@Jeremy yes, I know. Then again, you can always throw your entire system away and rebuild it from scratch, adding a real language to it, or if that is not an option, quit your job and go work for a place which is not making you waste your time solving lame problems. My answer was meant to encourage him to stop for a moment and reconsider what he is doing with his life. — Mike Nakis, Feb 04 '15 at 14:35
As Ditto said in Oracle I have used the regexp_replace. I need something simple as possible. — Edinei Raduvanski, Feb 04 '15 at 15:45
I wonder if there is any native function of SQL SERVER to separate each character of a string by commas or pipe. Example: value of string 'ABCDE'. I need something that returns 'A, B, C, D, E'. I do not want to create a new function, needs to be something native SQL SERVER. In oracle I can deal with a native function (regexp_replace ('ABCDE', '\ 1 |' '().') — Edinei Raduvanski, Feb 05 '15 at 00:30

score 2 · Answer 1 · answered Feb 04 '15 at 14:28

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

score 1 · Answer 2 · answered Feb 04 '15 at 14:16

1

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

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

answered Feb 04 '15 at 14:16

Jeremy

4,808
2
21
24

a free email registration is worth deep knowledge IMO. – Jeremy Feb 04 '15 at 15:10
still a link to a possible solution is a comment, not an answer. Note a link may not be valid in 2 years when someone attempts to use it – t-clausen.dk Feb 04 '15 at 15:11
I wonder if there is any native function of SQL SERVER to separate each character of a string by commas or pipe. Example: value of string 'ABCDE'. I need something that returns 'A, B, C, D, E'. I do not want to create a new function, needs to be something native SQL SERVER. In oracle I can deal with a native function (regexp_replace ('ABCDE', '\ 1 |' '().') – Edinei Raduvanski Feb 05 '15 at 00:29
2

@EdineiRaduvanski no, there is not. – Jeremy Feb 05 '15 at 03:55

score 1 · Answer 3 · answered Feb 04 '15 at 14:49

1

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

answered Feb 04 '15 at 14:49

t-clausen.dk

43,517
12
59
92

I wonder if there is any native function of SQL SERVER to separate each character of a string by commas or pipe. Example: value of string 'ABCDE'. I need something that returns 'A, B, C, D, E'. I do not want to create a new function, needs to be something native SQL SERVER. In oracle I can deal with a native function (regexp_replace ('ABCDE', '\ 1 |' '().') – Edinei Raduvanski Feb 05 '15 at 00:29
@EdineiRaduvanski no, there is not a native sql-server function for that. – t-clausen.dk Feb 06 '15 at 12:30

split a string by commas (,) SQL SERVER

3 Answers3