Split function equivalent in T-SQL

Question

I   m looking to split  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15      comma delimited  into a table or table variable     Does anyone have a function that returns each one in a row

User · Answer

CREATE FUNCTION Split      delimited nvarchar max      delimiter nvarchar 100    RETURNS  t TABLE   -- Id column can be commented out  not required for sql splitting string   id int identity 1 1   -- I use this column for numbering splitted parts   val nvarchar max    AS BEGIN   declare  xml xml   set  xml   N  lt root gt  lt r gt     replace  delimited  delimiter   lt  r gt  lt r gt        lt  r gt  lt  root gt      insert into  t val    select     r value      varchar max    as item   from  xml nodes    root r   as records r     RETURN END GO   usage  Select   from dbo Split N 1 2 3 4 6

User · Answer

Using tally table here is one split string function best possible approach  by Jeff Moden  CREATE FUNCTION  dbo   DelimitedSplit8K            pString VARCHAR 8000    pDelimiter CHAR 1   RETURNS TABLE WITH SCHEMABINDING AS  RETURN --       Inline  CTE Driven  Tally Table  produces values from 0 up to 10 000         -- enough to cover NVARCHAR 4000    WITH E1 N  AS                    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL                   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL                   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1                                             --10E 1 or 10 rows        E2 N  AS  SELECT 1 FROM E1 a  E1 b   --10E 2 or 100 rows        E4 N  AS  SELECT 1 FROM E2 a  E2 b   --10E 4 or 10 000 rows max  cteTally N  AS  --     This provides the  base  CTE and limits the number of rows right up front                      -- for both a performance gain and prevention of accidental  overruns                   SELECT TOP  ISNULL DATALENGTH  pString  0   ROW NUMBER   OVER  ORDER BY  SELECT NULL   FROM E4                    cteStart N1  AS  --     This returns N 1  starting position of each  element  just once for each delimiter                   SELECT 1 UNION ALL                  SELECT t N 1 FROM cteTally t WHERE SUBSTRING  pString t N 1     pDelimiter                    cteLen N1 L1  AS --     Return start and length  for use in substring                   SELECT s N1                          ISNULL NULLIF CHARINDEX  pDelimiter  pString s N1  0 -s N1 8000                     FROM cteStart s                   --      Do the actual split  The ISNULL NULLIF combo handles the length for the final element when no delimiter is found   SELECT ItemNumber   ROW NUMBER   OVER ORDER BY l N1           Item         SUBSTRING  pString  l N1  l L1     FROM cteLen l     Referred from Tally OH  An Improved SQL 8K    CSV Splitter    Function

User · Answer

Here is somewhat old-fashioned solution          Splits string into parts delimitered with specified character     CREATE FUNCTION  dbo   SDF SplitString         sString nvarchar 2048        cDelimiter nchar 1    RETURNS  tParts TABLE   part nvarchar 2048    AS BEGIN     if  sString is null return     declare  iStart int               iPos int     if substring   sString  1  1      cDelimiter      begin         set  iStart   2         insert into  tParts         values  null       end     else          set  iStart   1     while 1 1     begin         set  iPos   charindex   cDelimiter   sString   iStart           if  iPos   0             set  iPos   len   sString   1         if  iPos -  iStart  gt  0                       insert into  tParts             values    substring   sString   iStart   iPos- iStart            else             insert into  tParts             values  null           set  iStart    iPos 1         if  iStart  gt  len   sString                break     end     RETURN  END   In SQL Server 2008 you can achieve the same with  NET code  Maybe it would work faster  but definitely this approach is easier to manage

User · Answer

Try this  DECLARE  xml xml   str varchar 100    delimiter varchar 10  SET  str    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15  SET  delimiter       SET  xml   cast    lt X gt   replace  str   delimiter    lt  X gt  lt X gt      lt  X gt    as xml  SELECT C value       varchar 10    as value FROM  xml nodes  X   as X C    OR  DECLARE  str varchar 100    delimiter varchar 10  SET  str    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15  SET  delimiter        WITH cte AS       SELECT 0 a  1 b     UNION ALL     SELECT b  CHARINDEX  delimiter   str  b    LEN  delimiter      FROM CTE     WHERE b  gt  a   SELECT SUBSTRING  str  a  CASE WHEN b  gt  LEN  delimiter       THEN b - a - LEN  delimiter       ELSE LEN  str  - a   1 END  value       FROM cte WHERE a  gt  0   Many more ways of doing the same is here How to split comma delimited string

User · Answer

You ve tagged this SQL Server 2008 but future visitors to this question  using SQL Server 2016   will likely want to know about STRING SPLIT   With this new builtin function you can now just use   SELECT TRY CAST value AS INT  FROM   STRING SPLIT   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15           Some restrictions of this function and some promising results of performance testing are in this blog post by Aaron Bertrand

User · Answer

You write this function in sql server after that problem will be solved   http   csharpdotnetsol blogspot in 2013 12 csv-function-in-sql-server-for-divide html

User · Answer

I am tempted to squeeze in my favourite solution  The resulting table will consist of 2 columns  PosIdx for position of the found integer  and Value in integer    create function FnSplitToTableInt        param nvarchar 4000    returns table as return     with Numbers Number  as                select 1          union all          select Number   1 from Numbers where Number  lt  4000            Found as               select              Number as PosIdx              convert int  ltrim rtrim convert nvarchar 4000                    substring  param  Number                   charindex N    collate Latin1 General BIN                    param   N     Number  - Number      as Value         from                Numbers          where               Number  lt   len  param          and substring N       param  Number  1    N    collate Latin1 General BIN           select          PosIdx           case when isnumeric Value    1              then convert int  Value               else convert int  null  end as Value      from          Found   It works by using recursive CTE as the list of positions  from 1 to 100 by default  If you need to work with string longer than 100  simply call this function using  option  maxrecursion 4000   like the following    select   from FnSplitToTableInt        9  8  7  6  5  4  3  2  1  0            9  8  7  6  5  4  3  2  1  0           9  8  7  6  5  4  3  2  1  0           9  8  7  6  5  4  3  2  1  0           9  8  7  6  5  4  3  2  1  0     option  maxrecursion 4000

User · Answer

here  is the split function that u asked  CREATE FUNCTION  dbo   split              delimited NVARCHAR MAX              delimiter NVARCHAR 100            RETURNS  t TABLE  id INT IDENTITY 1 1   val NVARCHAR MAX           AS         BEGIN           DECLARE  xml XML           SET  xml   N  lt t gt     REPLACE  delimited  delimiter   lt  t gt  lt t gt        lt  t gt              INSERT INTO  t val            SELECT  r value      varchar MAX    as item           FROM   xml nodes   t   as records r            RETURN         END   execute the function like this  select   from dbo split  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

User · Answer

This is another version which really does not have any restrictions  e g   special chars when using xml approach  number of records in CTE approach  and it runs much faster based on a test on 10M  records with source string average length of 4000  Hope this could help    Create function  dbo   udf split         ListString nvarchar max        Delimiter  nvarchar 1000        IncludeEmpty bit   Returns  ListTable TABLE  ID int  ListValue nvarchar 1000   AS BEGIN     Declare  CurrentPosition int   NextPosition int   Item nvarchar max    ID int   L int     Select  ID   1      L   len replace  Delimiter                         ListString    ListString    Delimiter               CurrentPosition   1      Select  NextPosition   Charindex  Delimiter   ListString   CurrentPosition     While  NextPosition  gt  0 Begin    Set   Item   LTRIM RTRIM SUBSTRING  ListString   CurrentPosition   NextPosition- CurrentPosition       If       IncludeEmpty 1 or LEN  Item  gt 0 Begin       Insert Into  ListTable  ID  ListValue  Values   ID   Item       Set  ID    ID 1    End    Set   CurrentPosition    NextPosition  L    Set   NextPosition   Charindex  Delimiter   ListString   CurrentPosition    End     RETURN END

User · Answer

DECLARE      InputString NVARCHAR MAX     token1 token2 token3 token4 token5         delimiter varchar 10         DECLARE  xml AS XML   CAST    lt X gt   REPLACE  InputString  delimiter    lt  X gt  lt X gt      lt  X gt    AS XML  SELECT C value       varchar 10    AS value FROM  xml nodes  X   as X C    Source of this response  http   sqlhint com sqlserver how-to best-split-function-tsql-delimited

User · Answer

Object   UserDefinedFunction  dbo   Split     Script Date  10 04 2013 18 18 38     SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO ALTER FUNCTION  dbo   Split    List varchar 8000   SplitOn Nvarchar 5   RETURNS  RtnValue table  Id int identity 1 1  Value nvarchar 100   AS BEGIN     Set  List   Replace  List              While  Charindex  SplitOn  List  gt 0      Begin      Insert Into  RtnValue  value      Select     Value   ltrim rtrim Substring  List 1 Charindex  SplitOn  List -1         Set  List   Substring  List Charindex  SplitOn  List  len  SplitOn  len  List       End      Insert Into  RtnValue  Value      Select Value   ltrim rtrim  List        Return END go  Select   From  Clv   Split    1 2 3 3 3 3        GO

User · Answer

This is most like  NET  for those of you who are familiar with that function   CREATE FUNCTION dbo  String Split         Text VARCHAR MAX        Delimiter VARCHAR 100        Index INT   RETURNS VARCHAR MAX  AS BEGIN     DECLARE  A TABLE  ID INT IDENTITY  V VARCHAR MAX        DECLARE  R VARCHAR MAX       WITH CTE AS           SELECT 0 A  1 B     UNION ALL     SELECT B  CONVERT INT CHARINDEX  Delimiter   Text  B    LEN  Delimiter       FROM CTE     WHERE B  gt  A           INSERT  A V      SELECT SUBSTRING  Text A CASE WHEN B  gt  LEN  Delimiter  THEN B-A-LEN  Delimiter  ELSE LEN  Text  - A   1 END  VALUE           FROM CTE WHERE A  gt 0      SELECT       R                 V     FROM         A     WHERE       ID    Index   1     RETURN       R END  SELECT dbo  String Split   121 2 3 0      1  -- gives  2

User · Answer

CREATE Function  dbo   CsvToInt     Array varchar 4000    returns  IntTable table   IntValue int  AS begin declare  separator char 1  set  separator       declare  separator position int  declare  array value varchar 4000    set  array    array        while patindex          array   lt  gt  0  begin  select  separator position   patindex          array  select  array value   left  array   separator position - 1   Insert  IntTable Values  Cast  array value as int   select  array   stuff  array  1   separator position      end

User · Answer

This blog came with a pretty good solution using XML in T-SQL   This is the function I came up with based on that blog  change function name and result type cast per need    SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO CREATE FUNCTION  dbo   SplitIntoBigints    List varchar MAX    Splitter char  RETURNS TABLE  AS RETURN        WITH SplittedXML AS          SELECT CAST   lt v gt     REPLACE  List   Splitter    lt  v gt  lt v gt        lt  v gt   AS XML  AS Splitted           SELECT x v value       bigint   AS Value     FROM SplittedXML     CROSS APPLY Splitted nodes    v   x v    GO

User · Answer

This simple CTE will give what s needed   DECLARE  csv varchar max     1 2 3 4 5 6 7 8 9 10 11 12 13 14 15   --append comma to the list for CTE to work correctly SET  csv    csv        --remove double commas  empty entries  SET  csv   replace  csv              WITH CteCsv AS       SELECT CHARINDEX       csv  idx  SUBSTRING  csv  1  CHARINDEX       csv  - 1   Value      UNION ALL     SELECT CHARINDEX       csv  idx   1   SUBSTRING  csv  idx   1  CHARINDEX       csv  idx   1  - idx - 1  FROM CteCsv     WHERE CHARINDEX       csv  idx   1   gt  0    SELECT  Value  FROM CteCsv

[sql] Split function equivalent in T-SQL?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008