T-SQL split string

Question

I have a SQL Server 2008 R2 column containing a string which I need to split by a comma  I have seen many answers on StackOverflow but none of them works in R2  I have made sure I have select permissions on any split function examples  Any help greatly appreciated

User · Answer

I needed a quick way to get rid of the  4 from a zip code    UPDATE  Emails    SET ZIPCode   SUBSTRING ZIPCode  1   CHARINDEX  -   ZIPCODE -1      WHERE ZIPCode LIKE   -     No proc    no UDF    just one tight little inline command that does what it must  Not fancy  not elegant    Change the delimiter as needed  etc  and it will work for anything

User · Answer

I had to write something like this recently  Here s the solution I came up with  It s generalized for any delimiter string and I think it would perform slightly better   CREATE FUNCTION  dbo   SplitString          string nvarchar 4000         delim nvarchar 100    RETURNS      result TABLE             Value  nvarchar 4000  NOT NULL            Index  int NOT NULL   AS BEGIN     DECLARE  str nvarchar 4000               pos int               prv int   1      SELECT  pos   CHARINDEX  delim   string      WHILE  pos  gt  0     BEGIN         SELECT  str   SUBSTRING  string   prv   pos -  prv          INSERT INTO  result SELECT  str   prv          SELECT  prv    pos   LEN  delim                  pos   CHARINDEX  delim   string   pos   1      END      INSERT INTO  result SELECT SUBSTRING  string   prv  4000    prv     RETURN END

User · Answer

You can Use this function           CREATE FUNCTION SplitString                           Input NVARCHAR MAX               Character CHAR 1                          RETURNS  Output TABLE               Item NVARCHAR 1000                      AS         BEGIN        DECLARE  StartIndex INT   EndIndex INT       SET  StartIndex   1       IF SUBSTRING  Input  LEN  Input  - 1  LEN  Input    lt  gt   Character       BEGIN             SET  Input    Input    Character       END        WHILE CHARINDEX  Character   Input   gt  0       BEGIN             SET  EndIndex   CHARINDEX  Character   Input               INSERT INTO  Output Item              SELECT SUBSTRING  Input   StartIndex   EndIndex - 1               SET  Input   SUBSTRING  Input   EndIndex   1  LEN  Input         END        RETURN END GO

User · Answer

A recursive cte based solution    declare  T table  iden int identity  col1 varchar 100    insert into  T col1  values          ROOT South America Lima Test Test2            ROOT South America Peru Test Test2            ROOT  South America Venuzuala             RtT South America                ROOT South Americas        declare  split char 1         select  split as split  with cte as     select t iden  case when SUBSTRING REVERSE rtrim t col1    1  1     split then LTRIM RTRIM t col1   else LTRIM RTRIM t col1      split end  as col1  0 as pos                               1 as cnt    from  T t    union all     select t iden  t col1                                                                                                                                charindex  split  t col1  t pos   1   cnt   1     from cte t     where charindex  split  t col1  t pos   1   gt  0    select t1    t2 pos  t2 cnt        ltrim rtrim SUBSTRING t1 col1  t1 pos 1  t2 pos-t1 pos-1    as bingo from cte t1  join cte t2    on t2 iden   t1 iden   and t2 cnt    t1 cnt 1  and t2 pos  gt  t1 pos  order by t1 iden  t1 cnt

User · Answer

I take the xml route by wrapping the values into elements  M but anything works    declare  v nvarchar max     100 201 abcde   select      a value       varchar max    from      select cast   lt M gt     REPLACE  v         lt  M gt  lt M gt        lt  M gt   AS XML  as col  as A     CROSS APPLY A col nodes    M   AS Split a

User · Answer

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

User · Answer

If you need a quick ad-hoc solution for common cases with minimum code  then this recursive CTE two-liner will do it  DECLARE  s VARCHAR 200      1 2  3   4    5     WITH a AS  SELECT i -1  j 0 UNION ALL SELECT j  CHARINDEX       s  j   1  FROM a WHERE j  gt  i   b AS  SELECT SUBSTRING  s  i 1  IIF j gt 0  j  LEN  s  1 -i-1  s FROM a WHERE i  gt   0  SELECT   FROM b  Either use this as a stand-alone statement or just add the above CTEs to any of your queries and you will be able to join the resulting table b with others for use in any further expressions  edit  by Shnugo  If you add a counter  you will get a position index together with the List  DECLARE  s VARCHAR 200     1 2333 344 4    WITH a AS  SELECT n 0  i -1  j 0 UNION ALL SELECT n 1  j  CHARINDEX       s  j 1  FROM a WHERE j  gt  i   b AS  SELECT n  SUBSTRING  s  i 1  IIF j gt 0  j  LEN  s  1 -i-1  s FROM a WHERE i  gt   0  SELECT   FROM b   The result  n   s 1   1 2   2333 3   344 4   4

User · Answer

A solution using a CTE  if anyone should need that  apart from me  who obviously did  that is why I wrote it    declare  StringToSplit varchar 100     Test1 Test2 Test3   declare  SplitChar varchar 10          with StringToSplit as     select        ltrim  rtrim  substring   StringToSplit  1  charindex   SplitChar   StringToSplit   - 1       Head       substring   StringToSplit  charindex   SplitChar   StringToSplit     1  len   StringToSplit     Tail    union all    select       ltrim  rtrim  substring  Tail  1  charindex   SplitChar  Tail   - 1       Head       substring  Tail  charindex   SplitChar  Tail     1  len  Tail     Tail   from StringToSplit   where charindex   SplitChar  Tail    gt  0    union all    select       ltrim  rtrim  Tail     Head          Tail   from StringToSplit   where charindex   SplitChar  Tail     0     and len  Tail    gt  0   select Head from StringToSplit

User · Answer

The easiest way to do this is by using XML format   1  Converting string to rows without table  QUERY  DECLARE  String varchar 100     String1 String2 String3  -- To change     to any other delimeter  just change     to your desired one DECLARE  Delimiter CHAR            SELECT LTRIM RTRIM Split a value       VARCHAR 100       Value   FROM               SELECT CAST    lt M gt     REPLACE  String   Delimiter    lt  M gt  lt M gt        lt  M gt   AS XML  AS Data               AS A  CROSS APPLY Data nodes    M   AS Split a    RESULT   x---------x    Value      x---------x    String1      String2      String3    x---------x   2  Converting to rows from a table which have an ID for each CSV row  SOURCE TABLE   x-----x--------------------------x    Id              Value             x-----x--------------------------x     1     String1 String2 String3       2     String4 String5 String6         x-----x--------------------------x   QUERY  -- To change     to any other delimeter  just change     before   lt  M gt  lt M gt   to your desired one DECLARE  Delimiter CHAR        SELECT ID LTRIM RTRIM Split a value       VARCHAR 100       Value   FROM               SELECT ID CAST    lt M gt     REPLACE VALUE   Delimiter    lt  M gt  lt M gt        lt  M gt   AS XML  AS Data                  FROM TABLENAME   AS A  CROSS APPLY Data nodes    M   AS Split a    RESULT   x-----x----------x    Id     Value      x-----x----------x     1     String1       1     String2         1     String3       2     String4         2     String5       2     String6         x-----x----------x

User · Answer

This is based on Andy Robertson s answer  I needed a delimiter other than comma   CREATE FUNCTION dbo splitstring    stringToSplit nvarchar MAX    delim nvarchar max   RETURNS   returnList TABLE   value   nvarchar   MAX   AS BEGIN   DECLARE  value NVARCHAR max   DECLARE  pos INT   WHILE CHARINDEX  delim   stringToSplit   gt  0  BEGIN   SELECT  pos    CHARINDEX  delim   stringToSplit      SELECT  value   SUBSTRING  stringToSplit  1   pos - 1     INSERT INTO  returnList    SELECT  value    SELECT  stringToSplit   SUBSTRING  stringToSplit   pos   LEN  delim   LEN  stringToSplit  -  pos   END   INSERT INTO  returnList  SELECT  stringToSplit   RETURN END GO   And to use it   SELECT   FROM dbo splitstring  test1 test2 test3            Tested on SQL Server 2008 R2   EDIT  correct test code

User · Answer

Answer to T-SQL split string   Based on answers from Andy Robinson and AviG   Enhanced functionality ref  LEN function not including trailing spaces in SQL Server   This  file  should be valid as both a markdown file and an SQL file            CREATE FUNCTION dbo splitstring   --CREATE OR ALTER          stringToSplit NVARCHAR MAX        RETURNS  returnList TABLE   Item  NVARCHAR  MAX       AS BEGIN         DECLARE  name NVARCHAR MAX          DECLARE  pos BIGINT         SET  stringToSplit    stringToSplit                   -- this should allow entries that end with a     to have a blank value in that  column          WHILE   LEN  stringToSplit       gt  1   BEGIN           --        gets around LEN trimming terminal spaces  See URL referenced above             SET  pos   COALESCE NULLIF CHARINDEX       stringToSplit  0  LEN  stringToSplit       -- COALESCE grabs first non-null value             SET  name   SUBSTRING  stringToSplit  1   pos-1   --MAX size of string of type nvarchar is 4000              SET  stringToSplit   SUBSTRING  stringToSplit   pos 1  4000  -- With SUBSTRING fn  MS web    If start is greater than the number of characters in the value expression  a zero-length expression is returned               INSERT INTO  returnList SELECT  name --additional debugging parameters below can be added             --     pos     CAST  pos as nvarchar      remain        stringToSplit           CAST LEN  stringToSplit     -1 as nvarchar                END         RETURN     END     GO        Test cases  see URL referenced as  enhanced functionality  above  SELECT   LEN Item     -1  L  from splitstring  a  b     Item   L ---    --- a      1        0 b      1   SELECT   LEN Item     -1  L  from splitstring  a      Item   L    ---    --- a      1        0        0   SELECT   LEN Item     -1  L  from splitstring  a       Item   L    ---    --- a      1        0        1   SELECT   LEN Item     -1  L  from splitstring  a   c     Item   L    ---    --- a      1        0  c     3

User · Answer

The easiest way    Install SQL Server 2016 Use STRING SPLIT https   msdn microsoft com en-us library mt684588 aspx   It works even in express edition

User · Answer

I modified  Andy Robinson s function a little bit  Now you can select only required part from returning table   CREATE FUNCTION dbo splitstring    stringToSplit VARCHAR MAX     RETURNS    returnList TABLE   numOrder   tinyint     Name   nvarchar   500   AS BEGIN   DECLARE  name NVARCHAR 255    DECLARE  pos INT   DECLARE  orderNum INT   SET  orderNum 0   WHILE CHARINDEX       stringToSplit   gt  0   BEGIN     SELECT  orderNum  orderNum 1    SELECT  pos    CHARINDEX       stringToSplit      SELECT  name   SUBSTRING  stringToSplit  1   pos-1     INSERT INTO  returnList    SELECT  orderNum  name    SELECT  stringToSplit   SUBSTRING  stringToSplit   pos 1  LEN  stringToSplit - pos   END     SELECT  orderNum  orderNum 1   INSERT INTO  returnList  SELECT  orderNum   stringToSplit   RETURN END   Usage    SELECT Name FROM dbo splitstring  ELIS YD CRP1 1 CBA MDSP T389 BT   WHERE numOrder 5

User · Answer

All the functions for string splitting that use some kind of Loop-ing  iterations  have bad performance  They should be replaced with set-based solution   This code executes excellent   CREATE FUNCTION dbo SplitStrings       List       NVARCHAR MAX       Delimiter  NVARCHAR 255    RETURNS TABLE WITH SCHEMABINDING AS    RETURN               SELECT Item   y i value     text    1     nvarchar 4000          FROM                   SELECT x   CONVERT XML    lt i gt                REPLACE  List   Delimiter    lt  i gt  lt i gt                   lt  i gt    query              AS a CROSS APPLY x nodes  i   AS y i        GO

User · Answer

if you replace  WHILE CHARINDEX       stringToSplit   gt  0   with  WHILE LEN  stringToSplit   gt  0   you can eliminate that last insert after the while loop   CREATE FUNCTION dbo splitstring    stringToSplit VARCHAR MAX    RETURNS   returnList TABLE   Name   nvarchar   500   AS BEGIN   DECLARE  name NVARCHAR 255   DECLARE  pos INT   WHILE LEN  stringToSplit   gt  0  BEGIN   SELECT  pos    CHARINDEX       stringToSplit    if  pos   0         SELECT  pos   LEN  stringToSplit      SELECT  name   SUBSTRING  stringToSplit  1   pos-1     INSERT INTO  returnList    SELECT  name    SELECT  stringToSplit   SUBSTRING  stringToSplit   pos 1  LEN  stringToSplit - pos   END   RETURN END

User · Answer

This is more narrowly-tailored   When I do this I usually have a comma-delimited list of unique ids  INT or BIGINT   which I want to cast as a table to use as an inner join to another table that has a primary key of INT or BIGINT   I want an in-line table-valued function returned so that I have the most efficient join possible   Sample usage would be    DECLARE  IDs VARCHAR 1000    SET  IDs     99 206 124 8967 1 7 3 45234 2 889 987979     SELECT me Value  FROM dbo MyEnum me  INNER JOIN dbo GetIntIdsTableFromDelimitedString  IDs  ids ON me PrimaryKey   ids ID   I stole the idea from http   sqlrecords blogspot com 2012 11 converting-delimited-list-to-table html  changing it to be in-line table-valued and cast as INT   create function dbo GetIntIDTableFromDelimitedString            IDs VARCHAR 1000   --this parameter must start and end with a comma  eg   123 456                           --all items in list must be perfectly formatted or function will error   RETURNS TABLE AS  RETURN  SELECT     CAST SUBSTRING  IDs Nums number   1 CHARINDEX      IDs  Nums number 2   - Nums number - 1  AS INT  AS ID  FROM          master   dbo   spt values  Nums WHERE Nums Type    P   AND    Nums number BETWEEN 1 AND DATALENGTH  IDs  AND    SUBSTRING  IDs Nums number 1        AND    CHARINDEX      IDs  Nums number 1    gt  Nums number   GO

User · Answer

The often used approach with XML elements breaks in case of forbidden characters  This is an approach to use this method with any kind of character  even with the semicolon as delimiter   The trick is  first to use SELECT SomeString AS     FOR XML PATH     to get all forbidden characters properly escaped  That s the reason  why I replace the delimiter to a magic value to avoid troubles with   as delimiter   DECLARE  Dummy TABLE  ID INT  SomeTextToSplit NVARCHAR MAX   INSERT INTO  Dummy VALUES   1 N A amp B C D E  F     2 N  C   amp    D    lt C gt  D E  F     DECLARE  Delimiter NVARCHAR 10       --special effort needed  due to entities coding with   amp code      WITH Casted AS       SELECT              CAST N  lt x gt     REPLACE  SELECT REPLACE SomeTextToSplit  Delimiter N     Split me here       AS     FOR XML PATH      N     Split me here      N  lt  x gt  lt x gt      N  lt  x gt   AS XML  AS SplitMe     FROM  Dummy   SELECT Casted ID        x value N    N nvarchar max    AS Part  FROM Casted CROSS APPLY SplitMe nodes N  x   AS A x    The result  ID  Part 1   A amp B 1   C 1   D 1   E  F 2    C   amp   D  2    lt C gt  2   D 2   E  F

User · Answer

Here is an example that you can use as function or also you can put the same logic in procedure  --SELECT   from  dbo  fn SplitString    CREATE FUNCTION  dbo   fn SplitString    CSV VARCHAR MAX    Delimeter VARCHAR 100                RETURNS  retTable TABLE          value  VARCHAR MAX  NULL  AS  BEGIN  DECLARE         vCSV VARCHAR  MAX     CSV          vDelimeter VARCHAR  100     Delimeter   IF  vDelimeter       BEGIN     SET  vCSV   REPLACE  vCSV                     SET  vDelimeter   REPLACE  vDelimeter                 END   SET  vCSV   REPLACE REPLACE REPLACE REPLACE REPLACE  vCSV    amp      amp amp       lt      amp lt       gt      amp gt             amp apos            amp quot      DECLARE  xml XML   SET  xml     lt i gt     REPLACE  vCSV   vDelimeter    lt  i gt  lt i gt        lt  i gt     INSERT INTO  retTable SELECT        x i value       varchar max    AS COLUMNNAME   FROM  xml nodes    i  AS x i     RETURN  END

User · Answer

With all due respect to  AviG this is the bug free version of function deviced by him to return all the tokens in full    IF EXISTS  SELECT   FROM sys objects WHERE type    TF  AND name    TF SplitString   DROP FUNCTION  dbo   TF SplitString  GO  --                                               -- Author   AviG -- Amendments   Parameterize the delimeter and included the missing chars in last token - Gemunu Wickremasinghe -- Description  Tabel valued function that Breaks the delimeted string by given delimeter and returns a tabel having split results -- Usage -- select   from    dbo   TF SplitString   token1 token2        token969       -- 969 items should be returned -- select   from    dbo   TF SplitString   4672978261 4672978255       -- 2 items should be returned --                                               CREATE FUNCTION dbo TF SplitString     stringToSplit VARCHAR MAX       delimeter char         RETURNS   returnList TABLE   Name   nvarchar   500   AS BEGIN      DECLARE  name NVARCHAR 255      DECLARE  pos INT      WHILE LEN  stringToSplit   gt  0     BEGIN         SELECT  pos    CHARINDEX  delimeter   stringToSplit            if  pos   0         BEGIN             SELECT  pos   LEN  stringToSplit              SELECT  name   SUBSTRING  stringToSplit  1   pos            END         else          BEGIN             SELECT  name   SUBSTRING  stringToSplit  1   pos-1          END          INSERT INTO  returnList          SELECT  name          SELECT  stringToSplit   SUBSTRING  stringToSplit   pos 1  LEN  stringToSplit - pos      END   RETURN END

User · Answer

ALTER FUNCTION  dbo  func split string        input as varchar max        delimiter as varchar 10           RETURNS  result TABLE       id smallint identity 1 1       csv value varchar max  not null   AS BEGIN     DECLARE  pos AS INT      DECLARE  string AS VARCHAR MAX             WHILE LEN  input   gt  0     BEGIN                    SELECT  pos   CHARINDEX  delimiter  input            IF  pos lt  0              select  pos   len  input           IF  pos  lt  gt  LEN  input               SELECT  string   SUBSTRING  input  1   pos-1           ELSE             SELECT  string   SUBSTRING  input  1   pos            INSERT INTO  result SELECT  string          SELECT  input   SUBSTRING  input   pos len  delimiter   LEN  input - pos             END     RETURN   END

User · Answer

Personnaly I use this function    ALTER FUNCTION  dbo   CUST SplitString         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  gt  0           SELECT  Id    ROW NUMBER   OVER  ORDER BY  SELECT 1             Data    SUBSTRING  String stpos COALESCE NULLIF endpos 0  LEN  String  1 -stpos      FROM Split

User · Answer

There is a correct version on here but I thought it would be nice to add a little fault tolerance in case they have a trailing comma as well as make it so you could use it not as a function but as part of a larger piece of code  Just in case you re only using it once time and don t need a function  This is also for integers  which is what I needed it for  so you might have to change your data types   DECLARE  StringToSeperate VARCHAR 10  SET  StringToSeperate    1 2 5   --SELECT  StringToSeperate IDs INTO  Test  DROP TABLE  IDs CREATE TABLE  IDs  ID int    DECLARE  CommaSeperatedValue NVARCHAR 255       DECLARE  Position INT   LEN  StringToSeperate   --Add Each Value WHILE CHARINDEX       StringToSeperate   gt  0 BEGIN     SELECT  Position    CHARINDEX       StringToSeperate        SELECT  CommaSeperatedValue   SUBSTRING  StringToSeperate  1   Position-1       INSERT INTO  IDs      SELECT  CommaSeperatedValue      SELECT  StringToSeperate   SUBSTRING  StringToSeperate   Position 1  LEN  StringToSeperate - Position   END  --Add Last Value IF  LEN LTRIM RTRIM  StringToSeperate    gt 0  BEGIN     INSERT INTO  IDs     SELECT SUBSTRING  StringToSeperate  1   Position  END  SELECT   FROM  IDs

User · Answer

I have developed a double Splitter  Takes two split characters  as requested Here  Could be of some value in this thread seeing its the most referenced for queries relating to string splitting   CREATE FUNCTION uft DoubleSplitter           -- Add the parameters for the function here      String VARCHAR 4000         Splitter1 CHAR       Splitter2 CHAR   RETURNS  Result TABLE  Id INT MId INT SValue VARCHAR 4000   AS BEGIN DECLARE  FResult TABLE Id INT IDENTITY 1  1                      SValue VARCHAR 4000   DECLARE  SResult TABLE Id INT IDENTITY 1  1                      MId INT                     SValue VARCHAR 4000   SET  String    String  Splitter1  WHILE CHARINDEX  Splitter1   String   gt  0     BEGIN        DECLARE  WorkingString VARCHAR 4000    NULL         SET  WorkingString   SUBSTRING  String  1  CHARINDEX  Splitter1   String  - 1         --Print  workingString         INSERT INTO  FResult        SELECT CASE             WHEN  WorkingString      THEN NULL             ELSE  WorkingString             END         SET  String   SUBSTRING  String  LEN  WorkingString    2  LEN  String        END IF ISNULL  Splitter2                BEGIN        DECLARE  OStartLoop INT        DECLARE  OEndLoop INT         SELECT  OStartLoop   MIN Id                OEndLoop   MAX Id         FROM  FResult         WHILE  OStartLoop  lt    OEndLoop           BEGIN              DECLARE  iString VARCHAR 4000               DECLARE  iMId INT               SELECT  iString   SValue  Splitter2                      iMId   Id              FROM  FResult              WHERE Id    OStartLoop               WHILE CHARINDEX  Splitter2   iString   gt  0                 BEGIN                     DECLARE  iWorkingString VARCHAR 4000    NULL                      SET  IWorkingString   SUBSTRING  iString  1  CHARINDEX  Splitter2   iString  - 1                       INSERT INTO  SResult                     SELECT  iMId                           CASE                          WHEN  iWorkingString      THEN NULL                          ELSE  iWorkingString                          END                      SET  iString   SUBSTRING  iString  LEN  iWorkingString    2  LEN  iString                    END               SET  OStartLoop    OStartLoop   1           END        INSERT INTO  Result        SELECT MId AS PrimarySplitID              ROW NUMBER   OVER  PARTITION BY MId ORDER BY Mid  Id  AS SecondarySplitID               SValue        FROM  SResult     END ELSE     BEGIN        INSERT INTO  Result        SELECT Id AS PrimarySplitID              NULL AS SecondarySplitID              SValue        FROM  FResult     END RETURN   Usage   --FirstSplit SELECT   FROM uft DoubleSplitter  ValueA ValueB ValueC ValueD  ValueE amp ValueA ValueB ValueC   ValueE amp ValueA ValueB  ValueD       amp   NULL   --Second Split SELECT   FROM uft DoubleSplitter  ValueA ValueB ValueC ValueD  ValueE amp ValueA ValueB ValueC   ValueE amp ValueA ValueB  ValueD       amp          Possible Usage  Get second value of each split    SELECT fn SValue FROM uft DoubleSplitter  ValueA ValueB ValueC ValueD  ValueE amp ValueA ValueB ValueC   ValueE amp ValueA ValueB  ValueD        amp        AS fn WHERE fn mid   2

User · Answer

Instead of recursive CTEs and while loops  has anyone considered a more set-based approach  Note that this function was written for the question  which was based on SQL Server 2008 and comma as the delimiter  In SQL Server 2016 and above  and in compatibility level 130 and above   STRING SPLIT   is a better option   CREATE FUNCTION dbo SplitString      List     nvarchar max      Delim    nvarchar 255    RETURNS TABLE AS   RETURN   SELECT  Value  FROM           SELECT  Value    LTRIM RTRIM SUBSTRING  List   Number         CHARINDEX  Delim   List    Delim   Number   -  Number         FROM  SELECT Number   ROW NUMBER   OVER  ORDER BY name        FROM sys all columns  AS x WHERE Number  lt   LEN  List        AND SUBSTRING  Delim    List   Number   DATALENGTH  Delim  2     Delim       AS y      GO   If you want to avoid the limitation of the length of the string being  lt   the number of rows in sys all columns  9 980 in model in SQL Server 2017  much higher in your own user databases   you can use other approaches for deriving the numbers  such as building your own table of numbers  You could also use a recursive CTE in cases where you can t use system tables or create your own   CREATE FUNCTION dbo SplitString      List     nvarchar max      Delim    nvarchar 255    RETURNS TABLE WITH SCHEMABINDING AS    RETURN   WITH n n  AS  SELECT 1 UNION ALL SELECT n 1         FROM n WHERE n  lt   LEN  List          SELECT  Value    SUBSTRING  List  n          CHARINDEX  Delim   List    Delim  n  - n         FROM n WHERE n  lt   LEN  List        AND SUBSTRING  Delim    List  n  DATALENGTH  Delim  2     Delim       GO   But you ll have to append OPTION  MAXRECURSION 0   or MAXRECURSION  lt longest possible string length if  lt  32768 gt   to the outer query in order to avoid errors with recursion for strings   100 characters  If that is also not a good alternative then see this answer as pointed out in the comments    Also  the delimiter will have to be NCHAR  lt  1228   Still researching why    More on split functions  why  and proof that  while loops and recursive CTEs don t scale  and better alternatives  if splitting strings coming from the application layer    http   www sqlperformance com 2012 07 t-sql-queries split-strings http   www sqlperformance com 2012 08 t-sql-queries splitting-strings-now-with-less-t-sql https   sqlblog org 2010 07 07 splitting-a-list-of-integers-another-roundup

User · Answer

Finally the wait is over in SQL Server 2016 they have introduced Split string function   STRING SPLIT   select   From STRING SPLIT   a b        cs    All the other methods to split string like XML  Tally table  while loop  etc   has been blown away by this STRING SPLIT function    Here is an excellent article with performance comparison   Performance Surprises and Assumptions   STRING SPLIT

User · Answer

here is a version that can split on a pattern using patindex  a simple adaptation of the post above  I had a case where I needed to split a string that contained multiple separator chars    alter FUNCTION dbo splitstring    stringToSplit VARCHAR 1000    splitPattern varchar 10    RETURNS   returnList TABLE   Name   nvarchar   500   AS BEGIN   DECLARE  name NVARCHAR 255   DECLARE  pos INT   WHILE PATINDEX  splitPattern   stringToSplit    0  BEGIN   SELECT  pos    PATINDEX  splitPattern   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 select   from dbo splitstring  stringa stringb x y z               result looks like this  stringa stringb x y z

[sql] T-SQL split string

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008

Examples related to split