CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
akhrot
akhrot

Reputation: 445

Semicolon seperated value to other column in sql server

i have a table with a column have value seperated by semi colon. the concern is value in the column are not fixed. it starts from 1 and end upto 80 semicolon sepaeration. i am trying to put each individual value to seperate column

SQL SERVER 2008 code

DECLARE @Table TABLE(
        Val VARCHAR(50)
)

INSERT INTO @Table (Val) SELECT '2Xcalcium; kidney' union all SELECT     '3XMagnessium; liver' union all SELECT '2-ECG;3XSODIUM;DIALYSIS'

SELECT  *,
        CAST(LEFT(Val,CHARINDEX(';',Val)-1) AS VARCHAR) FirstValue,
        CAST(RIGHT(Val,LEN(Val) - CHARINDEX(';',Val)) AS VARCHAR) SecondValue
FROM    @Table

I tried the above code but this is limited to 2 semicolon only. please share your expertise.

Upvotes: 0

Views: 729

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try it like this:

DECLARE @Table TABLE(
        Val VARCHAR(50)
)

INSERT INTO @Table (Val) SELECT '2Xcalcium; kidney' union all SELECT     '3XMagnessium; liver' union all SELECT '2-ECG;3XSODIUM;DIALYSIS';

;WITH Splitted AS
(
    SELECT *
          ,CAST('<x>' + REPLACE(Val,';','</x><x>') + '</x>' AS XML)  ValuesAsXML
    FROM @Table
)
SELECT  *
       ,ValuesAsXML.value('x[1]','varchar(max)') AS FirstCol
       ,ValuesAsXML.value('x[2]','varchar(max)') AS SecondCol
       ,ValuesAsXML.value('x[3]','varchar(max)') AS ThirdCol
       ,ValuesAsXML.value('x[4]','varchar(max)') AS FourthCol
       ,ValuesAsXML.value('x[5]','varchar(max)') AS FifthCol
FROM    Splitted

The result

Val                     FirstCol     SecondCol  ThirdCol    FourthCol   FifthCol
2Xcalcium; kidney       2Xcalcium    kidney     NULL        NULL        NULL
3XMagnessium; liver     3XMagnessium liver      NULL        NULL        NULL
2-ECG;3XSODIUM;DIALYSIS 2-ECG        3XSODIUM   DIALYSIS    NULL        NULL

Upvotes: 1

Squirrel
Squirrel

Reputation: 24792

Most of the link provided extract the element into rows.
If you prefer to use your existing logic and extract the individual element into separate column, you can use multiple cascaded CROSS APPLY.

SELECT  t.Val,
    v1.V as V1, 
    v2.V as V2,
    v3.V as V3
FROM    @Table t
    cross apply
    (
        select  V   = LEFT(t.Val, CHARINDEX(';', t.Val + ';') - 1),
            Val = STUFF(t.Val, 1, CHARINDEX(';', t.Val + ';'), '')

    ) v1
    cross apply
    (
        select  V   = LEFT(v1.Val, CHARINDEX(';', v1.Val + ';') - 1),
            Val = STUFF(v1.Val, 1, CHARINDEX(';', v1.Val + ';'), '')

    ) v2
    cross apply
    (
        select  V   = LEFT(v2.Val, CHARINDEX(';', v2.Val + ';') - 1),
            Val = STUFF(v2.Val, 1, CHARINDEX(';', v2.Val + ';'), '')

    ) v3

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28930

From your question ,it seems that you have data in below format..This can be done easily with numbers table..

declare @string varchar(max)
set @string='s,t,a,c,k'



select substring(','+@string+',',n+1,charindex(',',','+@string+',',n+1)-n-1)
from 
numbers 
where n<=len(@string)
and substring(','+@string+',',n,1)=','

Output:

s
t
a
c
k

Few more Gems:
https://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable http://sqlperformance.com/2012/07/t-sql-queries/split-strings

Upvotes: 0

Related Questions