Split data from colon into new column

Question

I have got a scenario where I want to split my data from colon into new column. E.g YR:136;YR:50;JN:275;YM:138;IN:477;WO:150;G1:10;F2:10.

What I am looking for is this:

YR  136
YR   50
JN   275
YM   138

Can anybody tell me how to achieve this? Any help would be much appreciated.

How many columns appear in your output based on that CSV string? — Tim Biegeleisen, Apr 20 '18 at 08:05
Possible duplicate of [Split comma delimited string and insert to a table (int)](https://stackoverflow.com/questions/23438761/split-comma-delimited-string-and-insert-to-a-table-int) — DineshDB, Apr 20 '18 at 08:21

Squirrel · Answer 1 · 2018-04-23T13:13:19.700

2

for SQL 2016 and later you can use STRING_SPLIT. If not, search for CSV Spliter

declare @str varchar(100) = 'YR:136;YR:50;JN:275;YM:138;IN:477;WO:150;G1:10;F2:10'

select  parsename(replace(value, ':', '.'), 2),
        parsename(replace(value, ':', '.'), 1)
from    string_split(@str, ';')

edited Apr 23 '18 at 13:13

answered Apr 20 '18 at 08:09

Squirrel

23,507
4
34
32

`STRING_SPLIT` from `Sql-Server 2016` only. – Ullas Apr 20 '18 at 08:17

score 1 · Answer 2 · answered Apr 20 '18 at 08:19

Hope the string will in the specified format every time. What I did was, first copied the same string to a new variable and added an extra ; to the end. Then used a WHILE loop to split each part before each ;. Then used LEFT and RIGHT function to take the parts before and after : from the splitted part. And inserted those values to a table variable @t.

Code

declare @str as varchar(max) ='YR:136;YR:50;JN:275;YM:138;IN:477;WO:150;G1:10;F2:10';

declare @rows as int;
set @rows = len(@str) - len(replace(@str, ';',''));

declare @t as table([col1] varchar(100), [col2] int);

declare @i as int;
set @i = 0;

declare @str2 as varchar(max);
set @str2 = @str + ';';

while(@i <= @rows)
begin
    declare @col as varchar(100);
    set @col = left(@str2, charindex(';', @str2, 1) - 1);
    set @str2 = right(@str2, len(@str2) - charindex(';', @str2, 1));    
    insert into @t([col1], [col2])
    select 
        left(@col, charindex(':', @col, 1) - 1), 
        right(@col, charindex(':', reverse(@col), 1) - 1);
    set @i += 1;
end

select * from @t;

Find a demo here

score 0 · Answer 3 · edited Apr 20 '18 at 08:18

0

In SQL Server you can do that using STRING_SPLIT.

For more information read the documentation: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

edited Apr 20 '18 at 08:18

Ilyes

14,640
4
29
55

answered Apr 20 '18 at 08:04

CHillingInTheWooods

111
6

should be `STRING_SPLIT` – Squirrel Apr 20 '18 at 08:09

score 0 · Answer 4 · answered Apr 20 '18 at 08:11

First CREATE the function:

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO

Then try this:

DECLARE @X VARCHAR(100), @Val VARCHAR(250) = 'YR:136;YR:50;JN:275;YM:138;IN:477;WO:150;G1:10;F2:10'

SELECT LEFT(value,CHARINDEX(':',value) - 1)A
    ,RIGHT(value,CHARINDEX(':',REVERSE(value)) - 1)B
FROM dbo.fn_Split(@Val,';')

Output:

A   B
YR  136
YR  50
JN  275
YM  138
IN  477
WO  150
G1  10
F2  10

score 0 · Answer 5 · answered Apr 20 '18 at 08:26

--FOR SQL SERVER 2016+

DECLARE @str VARCHAR(100) = 'YR:136;YR:50;JN:275;YM:138;IN:477;WO:150;G1:10;F2:10'

SELECT    SUBSTRING(value,0,CHARINDEX(':',value,0)) Id 
        , SUBSTRING(value,CHARINDEX(':',value,0)+1,100) Vals
FROM    STRING_SPLIT(@str, ';')

--FOR Older SQL SERVER

DECLARE @x AS XML=''
SET @x = CAST('<A>'+ REPLACE(@str,';','</A><A>')+ '</A>' AS XML)
SELECT SUBSTRING(t.value('.', 'VARCHAR(MAX)') ,0,CHARINDEX(':',t.value('.', 'VARCHAR(MAX)') ,0)) Id  
     , SUBSTRING(t.value('.', 'VARCHAR(MAX)') ,CHARINDEX(':',t.value('.', 'VARCHAR(MAX)') ,0)+1,100) Vals
FROM @x.nodes('/A') AS x(t)

score 0 · Answer 6 · answered Apr 20 '18 at 09:53

Try this below function

CREATE FUNCTION [dbo].[udf_GetUnsplitGivenData]
(
@string nvarchar(max)
)
RETURNS  @OutTable TABLE
(
COl1 nvarchar(max),
COl2 nvarchar(max)
)
AS
BEGIN

    DECLARE @Temp AS TABLE
    (
    DATA nvarchar(max)
    )
    INSERT INTO @Temp
    SELECT @string


    INSERT INTO @OutTable(COl1,COl2)
    SELECT  SUBSTRING(Data,1,CHARINDEX(':',Data )-1) AS COl1,
            SUBSTRING(Data,CHARINDEX(':',Data )+1,LEN(Data)) AS COl2
    FROM
    (
    SELECT  Split.a.value('.','nvarchar(100)') AS Data 
    FROM
    (
    SELECT 
    CAST( '<S>'+REPLACE(Data,';','</S><S>')+'</S>' AS XML ) AS Data 
    FROM @Temp
    ) AS A
    CROSS APPLY data.nodes('S') AS Split(a)
    )dt


RETURN
END
SELECT * FROM [dbo].[udf_GetUnsplitGivenData] (
  'YR:136;YR:50;JN:275;YM:138;IN:477;WO:150;G1:10;F2:10')
GO

Result

COl1    COl2
------------
YR      136
YR      50
JN      275
YM      138
IN      477
WO      150
G1      10
F2      10

Split data from colon into new column

6 Answers6