CODEWITHSUNDEEP
sql-server-2008
James
James

Reputation: 506

splitting a delimited field into separate columns in same record

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'

Upvotes: 2

Views: 3400

Answers (2)

DirtyPaws
DirtyPaws

Reputation: 28

**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/

Upvotes: 1

mellamokb
mellamokb

Reputation: 56769

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

Upvotes: 1

Related Questions