T-SQL Split String Based on Delimiter

T-SQL split string based on delimiter

May be this will help you.

SELECT SUBSTRING(myColumn, 1, CASE CHARINDEX('/', myColumn)
            WHEN 0
                THEN LEN(myColumn)
            ELSE CHARINDEX('/', myColumn) - 1
            END) AS FirstName
    ,SUBSTRING(myColumn, CASE CHARINDEX('/', myColumn)
            WHEN 0
                THEN LEN(myColumn) + 1
            ELSE CHARINDEX('/', myColumn) + 1
            END, 1000) AS LastName
FROM MyTable

sql split string based on delimiter with varying numbers

Use a combination of SUBSTRING, CHARINDEX & LEN.

SELECT yourfield,
SUBSTRING(yourfield, 1,CHARINDEX('-', yourfield)-1) LEFTSIDE,
SUBSTRING(yourfield, CHARINDEX('-', yourfield)+1, LEN(yourfield)) RIGHTSIDE
FROM yourtable

Output

yourfield                   LEFTSIDE      RIGHTSIDE
10.159.101.0-10.159.101.255 10.159.101.0  10.159.101.255

SQL Fiddle: http://sqlfiddle.com/#!6/cb36f/3/0

Split String based on position and delimiters

First, as I wrote in my comment: The solution for this is to fix your broken data model. For more information, read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!

That being said, I know that a lot of times, for many different reasons, changing the database structure is not an option, even though this would solve a multitude of problems.

Now, based on your string manipulation functions, I assume you're working with SQL-Server (Please note that for every sql question you should always specify the rdbms and version, or use database-agnostic if you really need a cross-database solution).

So based on your assumption, your best bet is probably to use Jeff Moden's DelimitedSplit8K UDF to break the string into a table.

This function will return a table with two columns - one for the substring, and one for it's index in the source string - which is exactly what you need to start constructing the results you're after.

So based on that function, a couple of common table expressions, pivot and conditional aggregation, I came up with the following solution:

WITH CTE1 AS
(
    SELECT  Id,
            CAST(IIF(ItemNumber % 2 = 0, Item, NULL) as int) As Data, 
            IIF(ItemNumber % 2 = 1, Item, NULL) As Name, 
            IIF(ItemNumber % 2 = 0, ItemNumber - 1, ItemNumber) As PairNumber
    FROM  #Split
    CROSS APPLY dbo.DelimitedSplit8K(SplitString, ':')
    WHERE Item IS NOT NULL
    AND Item <> ''
), CTE2 AS
(
    SELECT Id, PairNumber, MAX(Data) As Data, MAX(Name) As Name
    FROM CTE1
    GROUP BY Id, PairNumber
)

, CTEPivot AS
(
    SELECT Id, [10], [20], [30], [40], [50]
    FROM 
    (
    SELECT Id, SUM(Data) As Data, Name
    FROM CTE2 
    GROUP BY Id, Name
    ) D
    PIVOT  
    (  
    AVG(Data) 
    FOR Name IN ([10], [20], [30], [40], [50])  
    ) AS PivotTable
)

SELECT  Id, 
        MAX([10]) As [10], 
        MAX([20]) As [20], 
        MAX([30]) As [30], 
        MAX([40]) As [40], 
        MAX([50]) As [50] 
FROM CTEPivot
GROUP BY Id;  

Results:

+----+----+----+----+----+----+
| Id | 10 | 20 | 30 | 40 | 50 |
+----+----+----+----+----+----+
| 1  | 1  | 7  |    |    |    |
+----+----+----+----+----+----+
| 2  |    | 1  |    |    |    |
+----+----+----+----+----+----+
| 3  | 3  |    |    |    |    |
+----+----+----+----+----+----+
| 4  |    |    | 4  | 1  | 3  |
+----+----+----+----+----+----+

You can see a live demo on rextester.

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

T-SQL split string

I've used this SQL before which may work for you:-

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

and to use it:-

SELECT * FROM dbo.splitstring('91,12,65,78,56,789')

T-SQL split on delimiter

Without a Parse Function

Declare @YourTable table (ID int,IDList varchar(Max))
Insert Into @YourTable values
(1,'123|456|789|012|345|320'),
(2,'123|456')

Select A.ID
      ,B.*
 From @YourTable A
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From (Select x = Cast(''+ replace((Select A.IDList as [*] For XML Path('')),'|','')+'' as xml).query('.')) as A  
                Cross Apply x.nodes('x') AS B(i)
             ) B

Returns

ID  RetSeq  RetVal
1   1       123
1   2       456
1   3       789
1   4       012
1   5       345
1   6       320
2   1       123
2   2       456

OR with the SUPER DUPER Parse (orig source listed below / couple of tweaks)

Select A.ID
      ,B.*
 From @YourTable A
 Cross Apply [dbo].[udf-Str-Parse-8K](A.IDList,'|') B

Would Return the same as above

CREATE FUNCTION [dbo].[udf-Str-Parse-8K] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
           cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ),
           cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
           cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)

    Select RetSeq = Row_Number() over (Order By A.N)
          ,RetVal = Substring(@String, A.N, A.L) 
    From   cte4 A
);
--Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Much faster than str-Parse, but limited to 8K
--Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')

Edit - Stand Alone

Declare @String varchar(max) = '123|456|789|012|345|320'
Declare @Delim  varchar(10)  = '|'

Select RetSeq = Row_Number() over (Order By (Select null))
      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast(''+ replace((Select @String as [*] For XML Path('')),@Delim,'')+'' as xml).query('.')) as A 
Cross Apply x.nodes('x') AS B(i)

How to split string with delimiter and get the first value

If you only interested in the first value, you don't need to use STRING_SPLIT(). You can simply use charindex() to find the first delimiter and use left() to extract it

SELECT *, left(CommaId, charindex('.', CommaId) - 1)
FROM   @commanTable

---

Edit : if you always wanted the nth value, you can do a cascade charindex(). Provided that the n is not too huge. Else use the function that I provided in the comment.

SELECT  *, 
        item1 = left(CommaId, p1.p - 1),
        item2 = substring(CommaId, p1.p + 1, p2.p - p1.p - 1)
FROM    @commanTable t
        cross apply
        (
            select  p = charindex('.', CommaId)
        ) p1
        cross apply
        (
            select  p = charindex('.', CommaId, p1.p + 1)
        ) p2

T-SQL splitting column on a delimiter

You can work with fixed offsets, since you defined that the string always starts with two, and ends with two characters.

Here is a full working example:

DECLARE @tmp TABLE (
  Merged nvarchar(max)
)

INSERT INTO @tmp SELECT N'AB/1234/10'
INSERT INTO @tmp SELECT N'AB/ANYNUMBEROF-CHARACTERS/10'

SELECT
  LEFT(Merged,2) AS Column1,
  SUBSTRING(Merged,4,LEN(Merged)-6) AS Column2,
  RIGHT(Merged,2) AS Column3
FROM @tmp

We subtract the length of the string minus a constant (6 = two chars left, two chars right, two slashes) to extract the variable-length part from the middle.

Result:

Column1 Column2                Column3
AB      1234                   10
AB      ANYNUMBEROF-CHARACTERS 10

SQL server split string into columns by delimiter (dynamic length)

An xml-based solution

declare @tmp table (STRING varchar(500))

insert into @tmp
values
 ('AA.0.HJ')
,('AABBCC.099.0')
,('0.91.JAH21')

;WITH Splitted
AS (
    SELECT STRING
        ,CAST('' + REPLACE(STRING, '.', '') + '' AS XML) AS Parts
    FROM @tmp
    )
SELECT STRING
    ,Parts.value(N'/x[1]', 'varchar(50)') AS [First]
    ,Parts.value(N'/x[2]', 'varchar(50)') AS [Second]
    ,Parts.value(N'/x[3]', 'varchar(50)') AS [Third] 
FROM Splitted;

Output:

---

Related Topics