Split string into new column

Question

There is a column containing following e.g. abcd/ef/g/hij. Characters between the / are dynamic not fix. I want to split in a select query the content into 4 separate new columns.

The already answered question is different, I want to split the content in a string seperated by / into new columns.

will you always have 3 `/` in your string or number of column can also be dynamic? — ughai, May 20 '15 at 13:23
I don't fully understand this. Are you sure you want columns and not rows? What rows of data will be allocated to the columns? Are you wanting the string split to create a temp / perm table and then insert into that? — Christian Barron, May 20 '15 at 13:35
question is closed just as i completed an answer and the link only describe how to split a varchar. Not distribute it in columns — t-clausen.dk, May 20 '15 at 14:00

score 3 · Answer 1 · answered May 20 '15 at 13:35

3

You can use REPLACE to replace '/' with '.'. Then use PARSENAME to get each separate part of the string:

CREATE TABLE #tmp (str VARCHAR(50))

INSERT INTO #tmp VALUES
('abcd/ef/g/hij'),
('1111111/222/33/4444')

SELECT PARSENAME(x.s, 4) AS [1], PARSENAME(x.s, 3) AS [2], 
       PARSENAME(x.s, 2) AS [3], PARSENAME(x.s, 1) AS [4]
FROM #tmp
CROSS APPLY (SELECT REPLACE(str, '/', '.')) AS x(s)

Output:

1       2   3   4
---------------------
abcd    ef  g   hij
1111111 222 33  4444

answered May 20 '15 at 13:35

Giorgos Betsos

71,379
9
63
98

interesting solution, although I wouldn't use it because it's not supposed to do what OP wants. It will not work for more than 4 parts and it also could return incorrect results for strings like `[a]/[a]/[a]/[asd]` – Roman Pekar May 20 '15 at 13:44
1

@Roman The OP states: *I will always have 3 /.* (see comment), The only problem is if there are `'.'` in `str` column. But there is a work-around for this too (.i.e to replace `'.'` with a special not-existent character before replacing `'/'`s). – Giorgos Betsos May 20 '15 at 13:46
what about `[` symbols which will be removed by your solution? – Roman Pekar May 20 '15 at 15:04
1

@RomanPekar Well, these symbols will be treated as delimiters by `PARSENAME` and will be thus left out of the returned string. This solution is probably the simplest *but* can be used *only* in case there are no `'.'`, `'['` or `']'` in `str` column. – Giorgos Betsos May 20 '15 at 18:13

score 0 · Answer 2 · answered May 20 '15 at 13:27

If you ask me, fastest ad-hoc method would be to turn your data into xml and use nodes() method:

declare @temp table (data nvarchar(max))

insert into @temp
select 'abcd/ef/g/hij' union all
select '1/2/3'

select t.data, n.c.value('.', 'nvarchar(max)')
from @temp as t
    outer apply (select cast('<t>' + replace(t.data, '/', '</t><t>') + '</t>' as xml) as data) as d
    outer apply d.data.nodes('t') as n(c)

score 0 · Answer 3 · answered May 20 '15 at 13:35

You need to find the position of the / characters using CHARINDEX and slice the string up that way. It will be a large expression, because to find the third slash, you need to use the 3rd parameter of CHARINDEX, passing the result of another CHARINDEX, which also has its 3rd parameter being used. Except for the last (fourth) fragment, you also need to use CHARINDEX to find and remove text after the next slash.

Something like this will extract the text after the third slash:

RIGHT(s, CHARINDEX('/', s, CHARINDEX('/', s, CHARINDEX('/', s)+1)+1)+1)

I leave the rest to you.

Split string into new column

3 Answers3