T-SQL Split String

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

T-SQL split string by - and space

Hopefully this is part of a normalization project. This data is breaking 1NF and one really should avoid that...

Try it like this

The advantages

typesafe values
ad-hoc SQL
set based

If you want you might use a CASE WHEN to check if the last part is NULL and place Part2 into Part3 in this case...

DECLARE @input table(teststring nvarchar(100));
INSERT INTO @input VALUES
(N'27364 - John Smith'),(N'27364 - John Andrew Smith');

WITH Splitted AS
(
    SELECT CAST(N'<x>' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(teststring,N' - ',N' '),N'&',N'&'),N'<',N'<'),N'>',N'>'),N' ',N'</x><x>') + N'</x>' AS XML) testXML
    FROM @input
)
SELECT testXML.value('/x[1]','int') AS Number
      ,testXML.value('/x[2]','nvarchar(max)') AS Part1 
      ,testXML.value('/x[3]','nvarchar(max)') AS Part2 
      ,testXML.value('/x[4]','nvarchar(max)') AS Part3 
FROM Splitted

The result

Number  Part1   Part2   Part3
27364   John    Smith   NULL
27364   John    Andrew  Smith

How to select split string from column

Using string_split() in concert with a CROSS APPLY... Use OUTER APPLY if you want to see NULL values

Select A.customerID
      ,Phone = B.value
 From  YourTable A
 Cross Apply string_split(A.Phone,',') B

SQL Server : Split string to row

With this TVF, you can supply the string to be split and delimiter. Furthermore, you get the sequence number which can be very useful for secondary processing.

Select [CODE COMBINATION]
      ,[USER] = B.RetVal
 From  YourTable A
 Cross Apply [dbo].[udf-Str-Parse](A.[USER],';') B

Returns

Sample Image

The Parse UDF

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    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(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

Now, another option is the Parse-Row UDF. Notice we return the parsed string in one row. Currently 9 positions, but it is easy to expand or contract.

Select [CODE COMBINATION]
      ,B.*
 From  YourTable A
 Cross Apply [dbo].[udf-Str-Parse-Row](A.[USER],';') B

Returns

Sample Image

The Parse Row UDF

CREATE FUNCTION [dbo].[udf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (
    Select Pos1 = xDim.value('/x[1]','varchar(max)')
          ,Pos2 = xDim.value('/x[2]','varchar(max)')
          ,Pos3 = xDim.value('/x[3]','varchar(max)')
          ,Pos4 = xDim.value('/x[4]','varchar(max)')
          ,Pos5 = xDim.value('/x[5]','varchar(max)')
          ,Pos6 = xDim.value('/x[6]','varchar(max)')
          ,Pos7 = xDim.value('/x[7]','varchar(max)')
          ,Pos8 = xDim.value('/x[8]','varchar(max)')
          ,Pos9 = xDim.value('/x[9]','varchar(max)')
     From (Select Cast('<x>' + Replace(@String,@Delimiter,'</x><x>')+'</x>' as XML) as xDim) A
)
--Select * from [dbo].[udf-Str-Parse-Row]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-Row]('John Cappelletti',' ')

How to split a string from a table SQL Server 2012?

Taking into account your link, this can be done by slightly changing the query by adding Cross Apply.

Select e.ID, t.a
From #Example As e Cross Apply ( 
SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA
FROM
(
    SELECT CAST('<X>'+REPLACE(e.List, ',', '</X><X>')+'</X>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a)) As t(a)

T-SQL split string by space?

Depending on the consistency of your data:

You can use SUBSTRING and CHARINDEX...

DECLARE @String varchar(10) = 'nl NED';
SELECT SUBSTRING(@String, CHARINDEX(' ', @String)+1, 10)

If you ONLY ever need the last 3 characters, then you could also use:

DECLARE @String varchar(10) = 'nl NED';
SELECT RIGHT(@String, 3)

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