Delimited Function in SQL to Split Data Between Semi-Colon

Delimited Function in SQL to Split Data between semi-colon

Try this:

SELECT
CASE WHEN PATINDEX('%B[0-9][0-9]%', Program)>0 THEN SUBSTRING(Program, PATINDEX('%B[0-9][0-9]%', Program) - 1, 4) 
     WHEN PATINDEX('%B[0-9]%', Program)>0 THEN SUBSTRING(Program, PATINDEX('%B[0-9]%', Program) - 1, 3) 
     ELSE '' END

FROM DataBase1 

First WHEN is responsible for extracting pattern B[0-9][0-9], i.e. when B is followed by two digits, second one is for extracting B followed by one digits. Default is returning empty string, when no match is found. If you are interested in extracting pattern B followed by three digits, you need to add another when (as the first case), enter pattern B[-9][0-9][0-9] instead of B[0-9][0-9] and change last number from 4 to 5 (length of string that is extracted).

PATINDEX returns position where the match is found.

split semicolon delimiter SQL to rows

If you do not like a function, or if you do not have the rights to create a new function, you can use the quite fast XML approach. In your case it needs some extra effort to get this XML-safe (due to special characters and the ; as delimiter):

Declare @Dummy table (ID int, SomeTextToSplit varchar(max))
Insert Into @Dummy values
 (1,'A&B;C;D;E, F')
,(2,'"C" & "D";<C>;D;E, F');

DECLARE @Delimiter VARCHAR(10)=';';
WITH Casted AS
(
    SELECT *
          ,CAST('<x>' + REPLACE((SELECT REPLACE(SomeTextToSplit,@Delimiter,'§§Split$me$here§§') AS [*] FOR XML PATH('')),'§§Split$me$here§§','</x><x>') + '</x>' AS XML) AS SplitMe
    FROM @Dummy
)
SELECT Casted.*
      ,x.value('.','nvarchar(max)') AS Part 
FROM Casted
CROSS APPLY SplitMe.nodes('/x') AS A(x)

The result

1   A&B
1   C
1   D
1   E, F
2   "C" & "D"
2   <C>
2   D
2   E, F

How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

Split semicolon-delimited column into multiple columns

Besides the fact that this is bad design here is a solution:

Just paste this into an empty query window and execute. Adapt to your needs...

declare @tbl TABLE(Column1 VARCHAR(15),Column2 VARCHAR(15),Column3 VARCHAR(150));
INSERT INTO @tbl VALUES
 ('ABC','123','User7;User9')  
,('nbm','qre','User1;User2;User3')  
,('POI','kjh','User1;User4;User5;User9');

WITH Splitted AS
(
    SELECT Column1,Column2,CAST('<x>'+REPLACE(Column3,';','</x><x>')+'</x>' AS XML) AS Col3Splitted
    FROM @tbl
) 
SELECT Column1,Column2,Col3Splitted
                      ,Col3Splitted.value('x[1]','varchar(max)') AS Column4
                      ,Col3Splitted.value('x[2]','varchar(max)') AS Column5
                      ,Col3Splitted.value('x[3]','varchar(max)') AS Column6
                      ,Col3Splitted.value('x[4]','varchar(max)') AS Column7
                      /*Add as many as you need*/
FROM Splitted 

Following the discussion with @SeanLang I add this dynamic approach. It will count the highest number of semicolons in Column3 and build the statement above dynamically.

CREATE TABLE #tbl (Column1 VARCHAR(15),Column2 VARCHAR(15),Column3 VARCHAR(150));
INSERT INTO #tbl VALUES
 ('ABC','123','User7;User9')  
,('nbm','qre','User1;User2;User3')  
,('POI','kjh','User1;User4;User5;User9');

DECLARE @sql VARCHAR(MAX)=
'WITH Splitted AS
(
    SELECT Column1,Column2,CAST(''<x>''+REPLACE(Column3,'';'',''</x><x>'')+''</x>'' AS XML) AS Col3Splitted
    FROM #tbl
) 
SELECT Column1,Column2';
DECLARE @counter INT = 0;
WHILE @counter<=(SELECT MAX(LEN(Column3) - LEN(REPLACE(Column3, ';', ''))) from #tbl)
BEGIN
    SET @counter=@counter+1;
    SET @sql=@sql+',Col3Splitted.value(''x[' +  CAST(@counter AS VARCHAR(10)) + ']'',''varchar(max)'') AS Column' + CAST(@counter+3 AS VARCHAR(10));
END
SET @sql=@sql+ ' FROM Splitted;';

EXEC (@sql);  

DROP TABLE #tbl;

How do I split one string value separated by semicolon and insert it into 3 separate columns using sql server?

Pretty ugly looking solution but will do the job

Query

DECLARE @Var NVARCHAR(100) = '7;132030;001202'

SELECT  LEFT(@Var, CHARINDEX(';', @Var) - 1)     AS [Department]
        ,SUBSTRING(@Var, CHARINDEX(';', @Var) + 1, 
         LEN(@Var)- LEN(LEFT(@Var, CHARINDEX(';', @Var)))- 
         LEN(RIGHT(@Var, CHARINDEX(';', REVERSE(@Var))))) AS [Job]
       , RIGHT(@Var, CHARINDEX(';', REVERSE(@Var))-1) AS [CostCode]

Result Set

Department  Job     CostCode
    7       132030  001202

Data Into Destination Table

INSERT INTO Destination_Table_Name (Department, Job, CostCode)

SELECT  LEFT(misc2, CHARINDEX(';', misc2) - 1)   
        ,SUBSTRING(misc2, CHARINDEX(';', misc2) + 1, 
         LEN(misc2)- LEN(LEFT(misc2, CHARINDEX(';', misc2)))- 
         LEN(RIGHT(misc2, CHARINDEX(';', REVERSE(misc2))))) 
       , RIGHT(misc2, CHARINDEX(';', REVERSE(misc2))-1) 
 FROM Source_Table

Delimited Semi-Colon Text in SQL with Accumulated Count

First create a custom split function

        CREATE FUNCTION [dbo].[Split]
        (
            @String NVARCHAR(4000),
            @Delimiter NCHAR(1)
        )
        RETURNS TABLE 
        AS
        RETURN 
        (
            WITH Split(stpos,endpos) 
            AS(
                SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
                UNION ALL
                SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
                    FROM Split
                    WHERE endpos > 0
            )
            SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
                'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
            FROM Split
        )

Than you can use this query to make the selection:

    declare @t table (Program Varchar(max), Filecount integer)

    insert into @t(Program ,   FileCount)
    select 'B1 ',  1
    union all select 'A1;B2', 2
    union all select 'A2;B3', 1
    union all select 'A3;C1;B4', 1
    union all select 'A3;C2;D1;B5;B6', 3
    union all select 'C3;D2;B7', 1
    union all select 'B8;B9', 2
    union all select 'B81;B9', 2

    SELECT DISTINCT s.Data, t.Filecount
    FROM @t t
    CROSS APPLY dbo.Split(t.Program, ';') s
    WHERE Data like 'B%'

Oracle SQL Select a Variable and split it by semicolon

OK, you have semi-colon separated list of values. You said that you want to have them in different rows, but - that's not what example shows ... this:

I would like it to look like this when using Select:

test test1 test2 test3

is only one row, with space as a separator.

---

Anyway: presuming that you really want different rows, then replace current separator with a line feed character (chr(10)), e.g.

SQL> select replace('test;test1;test2;test3', ';', chr(10)) result
  2  from dual;

RESULT
----------------------
test
test1
test2
test3

SQL>

---

As it turns out you need different columns after all, then - with such a sample data - regular expressions are a simple solution:

SQL> with test (col) as
  2    (select 'test;test1;test2;test3' from dual)
  3  select regexp_substr(col, '\w+', 1, 1) col1,
  4         regexp_substr(col, '\w+', 1, 2) col2,
  5         regexp_substr(col, '\w+', 1, 3) col3,
  6         regexp_substr(col, '\w+', 1, 4) col4
  7  from test;

COL1 COL2  COL3  COL4
---- ----- ----- -----
test test1 test2 test3

SQL>

---

Related Topics