How to Split String Using Delimiter Char Using T-Sql

How to split string using delimiter char using T-SQL?

For your specific data, you can use

Select col1, col2, LTRIM(RTRIM(SUBSTRING(
    STUFF(col3, CHARINDEX('|', col3,
    PATINDEX('%|Client Name =%', col3) + 14), 1000, ''),
    PATINDEX('%|Client Name =%', col3) + 14, 1000))) col3
from Table01

EDIT - charindex vs patindex

Test

select col3='Clent ID = 4356hy|Client Name = B B BOB|Client Phone = 667-444-2626|Client Fax = 666-666-0151|Info = INF8888877 -MAC333330554/444400800'
into t1m
from master..spt_values a
cross join master..spt_values b
where a.number < 100
-- (711704 row(s) affected)

set statistics time on

dbcc dropcleanbuffers
dbcc freeproccache
select a=CHARINDEX('|Client Name =', col3) into #tmp1 from t1m
drop table #tmp1

dbcc dropcleanbuffers
dbcc freeproccache
select a=PATINDEX('%|Client Name =%', col3) into #tmp2 from t1m
drop table #tmp2

set statistics time off

Timings

CHARINDEX:

 SQL Server Execution Times (1):
   CPU time = 5656 ms,  elapsed time = 6418 ms.
 SQL Server Execution Times (2):
   CPU time = 5813 ms,  elapsed time = 6114 ms.
 SQL Server Execution Times (3):
   CPU time = 5672 ms,  elapsed time = 6108 ms.

PATINDEX:

 SQL Server Execution Times (1):
   CPU time = 5906 ms,  elapsed time = 6296 ms.
 SQL Server Execution Times (2):
   CPU time = 5860 ms,  elapsed time = 6404 ms.
 SQL Server Execution Times (3):
   CPU time = 6109 ms,  elapsed time = 6301 ms.

Conclusion

The timings for CharIndex and PatIndex for 700k calls are within 3.5% of each other, so I don't think it would matter whichever is used. I use them interchangeably when both can work.

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

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')

How to split a string with multiple special characters or delimiters into separate fragments using SQL?

If you're using SQL Server 2017+ it provides translate that can help here combined with string_split:

with sample as (
 select 'abcd_45dl/beta3,test' StringCol union all
 select 'a56d/beta_46ab'
)
select *
from sample
cross apply String_Split(Translate(StringCol,'_/',',,'),',')

SQL Split function using two characters delimiter

One method is to use string_split() -- with a twist because it does not support multi-character separators:

select *
from string_split( replace(@str, ' OR ', '|'), '|')

Note: This assumes that | is not a valid "word" character.

Here is a db<>fiddle.

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

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('<x>'+ replace((Select A.IDList as [*] For XML Path('')),'|','</x><x>')+'</x>' 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('<x>'+ replace((Select @String as [*] For XML Path('')),@Delim,'</x><x>')+'</x>' as xml).query('.')) as A 
Cross Apply x.nodes('x') AS B(i)

How to Split String by Character into Separate Columns in SQL Server

There are probably several different ways to do it, some uglier than others. Here's one:

(Note: dat = the string of characters)

select *,
  substring(dat,1,charindex('-',dat)-1) as Section,
  substring(dat,charindex('-',dat)+1,charindex('-',dat)-1) as TownShip,
  reverse(substring(reverse(dat),0,charindex('-',reverse(dat)))) as myRange
from myTable

T-SQL split string containing alpha and numeric characters by variable delimiter

If you have SQL Server 2016+ you may try to use the following solution, based on JSON. The important part is to transform the input data into a valid JSON object (X1Y45Z1 is transformed into {"X":1,"Y":45,"Z":1} for example). After that you need to parse this object with OPENJSON() function using the appropriate WITH clause to define the columns in the output.

Table:

CREATE TABLE Data (
   TextData nvarchar(100)
)
INSERT INTO Data
   (TextData)
VALUES
   ('X1Y45Z1'),
   ('Y25Z1'),
   ('X1Y9Z1'),
   ('X2Z6'),
   ('Z1X6')

Statement:

SELECT d.TextData, j.*
FROM Data d
CROSS APPLY OPENJSON(
   CONCAT(
      N'{',
      STUFF(REPLACE(REPLACE(REPLACE(d.TextData, N'X', N',"X":'), N'Y', N',"Y":'), N'Z', N',"Z":'), 1, 1, N''),
      N'}'
   )   
) WITH (
   X int '$.X',
   Y int '$.Y',
   Z int '$.Z'
) j

Output:

---------------------
TextData    X   Y   Z
---------------------
X1Y45Z1     1   45  1
Y25Z1           25  1
X1Y9Z1      1   9   1
X2Z6        2       6
Z1X6        6       1

For versions before SQL Server 2016, you may use an XML based approach. You need to transform text data into an appropriate XML (X1Y45Z1 is transformed into <row><name>X</name><value>1</value></row><row><name>Y</name><value>45</value></row><row><name>Z</name><value>1</value></row> for example):

SELECT
   TextData,
   XmlData.value('(/row[name = "X"]/value/text())[1]', 'nvarchar(4)') AS X,
   XmlData.value('(/row[name = "Y"]/value/text())[1]', 'nvarchar(4)') AS Y,
   XmlData.value('(/row[name = "Z"]/value/text())[1]', 'nvarchar(4)') AS Z
FROM (
   SELECT
      TextData, 
      CONVERT(
         xml, 
      CONCAT(
           STUFF(REPLACE(REPLACE(REPLACE(d.TextData, N'X', N'</value></row><row><name>X</name><value>'), N'Y', N'</value></row><row><name>Y</name><value>'), N'Z', N'</value></row><row><name>Z</name><value>'), 1, 14, N''),
           N'</value></row>'
         )
      ) AS XmlData
   FROM Data d
) x

---

Related Topics