splitting a delimited field into separate columns in same record

Question

I need to split a pipe-delimited field ('Y|N|Y|Y').

I found the recursive function listed here T-SQL: Opposite to string concatenation - how to split string into multiple records

but it creates the values into new records.

row   field
 1      Y
 2      N
 3      Y
 4      N

I need to transform 'Y|N|Y|Y'

field
'Y|N|Y|Y'

into

field1  |  field2  | field3  | field4
    y         N         Y        N

Can someone point me in the right direction? Would it make it easier if I said the number of values is fixed (it will probably be 8 values delimited in one field).

Update: A possible field value could be this (note the blank value): 'Y|10|N|1||Y'

Yes, saying that the number of values is fixed makes the problem *much* easier. Are the widths of the fields also fixed, i.e., always Y/N? — mellamokb, Sep 27 '11 at 19:04
The widths will always be either one or two characters. Either a y/n value or a 2 digit number. — James, Sep 27 '11 at 19:54

mellamokb · Answer 1 · 2011-09-27T19:14:34.977

If the widths of the fields are always fixed as well, i.e., 1 character (Y/N), then it's as simple as using the substring function to get all the individual field values:

;with Data as (
    select 'Y|N|Y|Y' as choices union
    select 'Y|Y|Y|Y' as choices union
    select 'Y|N|N|Y' as choices union
    select 'Y|N|N|N' as choices union
    select 'N|N|Y|N' as choices union
    select 'Y|Y|N|Y' as choices
)
select
    substring(choices, 1, 1) as field1,
    substring(choices, 3, 1) as field2,
    substring(choices, 5, 1) as field3,
    substring(choices, 7, 1) as field4
from
    Data

Output:

field1  field2  field3  field4
N       N       Y       N
Y       N       N       N
Y       N       N       Y
Y       N       Y       Y
Y       Y       N       Y
Y       Y       Y       Y

If you can't guarantee that the widths of the fields are the same, you can make use of charindex and a helper table of field indexes to generate the output you are looking for. This becomes very verbose as the number of fields grows larger, but you should only have to write it once if the number of fields is going to be fixed:

;with Data as (
    select 1 as id, 'Y|N|Y|Y' as choices union
    select 2,'Y|Y|Y|Y' as choices union
    select 3,'Y|No|N|Y' as choices union
    select 4,'Yes|N|N|N' as choices union
    select 5,'N|N|Yes|No' as choices union
    select 6,'Y|Y|N|Yes' as choices
), Fields as (
    select
        id,
        charindex('|', choices) as field1end,
        charindex('|', choices, charindex('|', choices) + 1) as field2end,
        charindex('|', choices, charindex('|', choices, charindex('|', choices) + 1) + 1) as field3end,
        len(choices) + 1 as field4end
    from
        Data
)
select
    substring(choices, 1, field1end - 1) as field1,
    substring(choices, field1end + 1, field2end - field1end - 1) as field2,
    substring(choices, field2end + 1, field3end - field2end - 1) as field3,
    substring(choices, field3end + 1, field4end - field3end - 1) as field4
from
    Data D
inner join
    Fields F on D.id = F.id

Output:

field1  field2  field3  field4
Y       N       Y       Y
Y       Y       Y       Y
Y       No      N       Y
Yes     N       N       N
N       N       Yes     No
Y       Y       N       Yes

Thanks, I will take a look at this. Update: It could be 0, 1, or 2 characters long. A possible field value could be this: 'Y|10|N|1||Y' — James, Sep 27 '11 at 20:05

DirtyPaws · Accepted Answer · 2011-09-27T21:27:10.903

**EDIT: **Mine still works for the sample string

Here's an alternative answer, since you know the widths are a fixed length:

DECLARE @myString AS nvarchar(20) = 'Y|10|N|1||Y'

;WITH cte
AS
(
SELECT
    KeyCol = @@IDENTITY,
    CONVERT(XML,'<i>' + REPLACE(@myString, '|', '</i><i>') + '</i>') AS delimited_str
)
SELECT 
    [1] AS Field1,
    [2] AS Field2,
    [3] AS Field3,
    [4] AS Field4,
    [5] AS Field5,
    [6] AS Field6,
    [7] AS Field7,
    [8] AS Field8
FROM(
    SELECT 
        KeyCol,
        ROW_NUMBER() OVER (partition by KeyCol order by KeyCol)as col_nbr,
        x.i.value('.', 'VARCHAR(50)') AS delimited_VAL
    FROM cte
    CROSS APPLY delimited_str.nodes('//i') AS x(i)
    ) as PivotedDataTable
PIVOT
(MAX(delimited_VAL) FOR col_nbr IN
([1], [2], [3], [4], [5], [6], [7], [8])
) AS PivotTable;

EDIT: I knew I saw this somewhere before when I came across a similar problem: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/381e4164-f1e0-4b54-828f-2795d2cdcb3e/

splitting a delimited field into separate columns in same record

2 Answers2