How do I split a string so I can access item x

Question

Using SQL Server  how do I split a string so I can access item x   Take a string  Hello John Smith   How can I split the string by space and access the item at index 1 which should return  John

User · Answer

A modern approach using STRING SPLIT  requires SQL Server 2016 and above   DECLARE  string varchar 100     Hello John Smith   SELECT     ROW NUMBER   OVER  ORDER BY value  AS RowNr      value FROM string split  string         Result   RowNr   value 1       Hello 2       John 3       Smith   Now it is possible to get th nth element from the row number

User · Answer

What about using string and values   statement   DECLARE  str varchar max  SET  str    Hello John Smith   DECLARE  separator varchar max  SET  separator        DECLARE  Splited TABLE id int IDENTITY 1 1   item varchar max    SET  str   REPLACE  str   separator             SET  str    SELECT   FROM  VALUES        str         AS V A     INSERT INTO  Splited EXEC  str   SELECT   FROM  Splited   Result-set achieved   id  item 1   Hello 2   John 3   Smith

User · Answer

Here s my solution that may help someone  Modification of Jonesinator s answer above   If I have a string of delimited INT values and want a table of INTs returned  Which I can then join on    e g   1 20 3 343 44 6 8765   Create a UDF   IF OBJECT ID N dbo ufn GetIntTableFromDelimitedList   N TF   IS NOT NULL     DROP FUNCTION dbo  ufn GetIntTableFromDelimitedList   GO  CREATE FUNCTION dbo  ufn GetIntTableFromDelimitedList   String NVARCHAR MAX                    Delimiter CHAR 1    RETURNS  table TABLE        Value INT NOT NULL   AS  BEGIN DECLARE  Pattern NVARCHAR 3  SET  Pattern          Delimiter       DECLARE  Value NVARCHAR MAX   WHILE LEN  String   gt  0     BEGIN         IF PATINDEX  Pattern   String   gt  0         BEGIN             SET  Value   SUBSTRING  String  0  PATINDEX  Pattern   String               INSERT INTO  table  Value  VALUES   Value               SET  String   SUBSTRING  String  LEN  Value    Delimiter    1  LEN  String           END         ELSE         BEGIN             -- Just the one value              INSERT INTO  table  Value  VALUES   String              RETURN         END     END  RETURN END GO   Then get the table results   SELECT   FROM dbo  ufn GetIntTableFromDelimitedList   1 20 3 343 44 6 8765         1 20 3 343 44 6 8765   And in a join statement   SELECT  ID    FirstName  FROM  User  u JOIN dbo  ufn GetIntTableFromDelimitedList   1 20 3 343 44 6 8765        t ON u  ID    t  Value   1    Elvis 20   Karen 3    David 343  Simon 44   Raj 6    Mike 8765 Richard   If you want to return a list of NVARCHARs instead of INTs then just change the table definition   RETURNS  table TABLE        Value NVARCHAR MAX  NOT NULL

User · Answer

The following example uses a recursive CTE  Update 18 09 2013  CREATE FUNCTION dbo SplitStrings CTE  List nvarchar max    Delimiter nvarchar 1   RETURNS  returns TABLE  val nvarchar max    level  int  PRIMARY KEY CLUSTERED  level    AS BEGIN  WITH cte AS      SELECT SUBSTRING  List  0  CHARINDEX  Delimiter    List    Delimiter   AS val           CAST STUFF  List    Delimiter  1  CHARINDEX  Delimiter   List    Delimiter       AS nvarchar max   AS stval            1 AS  level    UNION ALL   SELECT SUBSTRING stval  0  CHARINDEX  Delimiter  stval             CAST STUFF stval  1  CHARINDEX  Delimiter  stval       AS nvarchar max              level    1   FROM cte   WHERE stval             INSERT  returns   SELECT REPLACE val           AS val   level    FROM cte   WHERE val  gt       RETURN END   Demo on SQLFiddle

User · Answer

You can split a string in SQL without needing a function   DECLARE  bla varchar MAX  SET  bla    BED40DFC-F468-46DD-8017-00EF2FA3E4A4 64B59FC5-3F4D-4B0E-9A48-01F3D4F220B0 A611A108-97CA-42F3-A2E1-057165339719 E72D95EA-578F-45FC-88E5-075F66FD726C   -- http   stackoverflow com questions 14712864 how-to-query-values-from-xml-nodes SELECT      x XmlCol value       varchar 36    AS val  FROM        SELECT      CAST   lt e gt     REPLACE  bla         lt  e gt  lt e gt        lt  e gt   AS xml  AS RawXml   AS b  CROSS APPLY b RawXml nodes  e   x XmlCol     If you need to support arbitrary strings  with xml special characters   DECLARE  bla NVARCHAR MAX  SET  bla     lt html gt unsafe  amp  safe Utf8CharsDon  tGetEncoded        -  Conex  lt html gt  Barnes  amp  Noble abc def ghi   -- http   stackoverflow com questions 14712864 how-to-query-values-from-xml-nodes SELECT      x XmlCol value       nvarchar MAX    AS val  FROM        SELECT      CAST   lt e gt     REPLACE  SELECT  bla FOR XML PATH              lt  e gt  lt e gt        lt  e gt   AS xml  AS RawXml   AS b  CROSS APPLY b RawXml nodes  e   x XmlCol

User · Answer

If your database has compatibility level of 130 or higher then you can use the STRING SPLIT function along with OFFSET FETCH clauses to get the specific item by index   To get the item at index N  zero based   you can use the following code  SELECT value FROM STRING SPLIT  Hello John Smith       ORDER BY  SELECT NULL  OFFSET N ROWS FETCH NEXT 1 ROWS ONLY   To check the compatibility level of your database  execute this code   SELECT compatibility level   FROM sys databases WHERE name    YourDBName

User · Answer

I realize this is a really old question  but starting with SQL Server 2016 there are functions for parsing JSON data that can be used to specifically address the OP s question--and without splitting strings or resorting to a user-defined function  To access an item at a particular index of a delimited string  use the JSON VALUE function  Properly formatted JSON data is required  however  strings must be enclosed in double quotes   and the delimiter must be a comma    with the entire string enclosed in square brackets      DECLARE  SampleString NVARCHAR MAX      Hello John Smith    --Format as JSON data  SET  SampleString         REPLACE  SampleString                     SELECT      JSON VALUE  SampleString     0    AS Element1Value      JSON VALUE  SampleString     1    AS Element2Value      JSON VALUE  SampleString     2    AS Element3Value    Output  Element1Value         Element2Value       Element3Value --------------------- ------------------- ------------------------------ Hello                 John                Smith   1 row affected

User · Answer

while similar to the xml based answer by josejuan  i found that processing the xml path only once  then pivoting was moderately more efficient   select ID       3  as PathProvidingID       4  as PathProvider       5  as ComponentProvidingID       6  as ComponentProviding       7  as InputRecievingID       8  as InputRecieving       9  as RowsPassed       10  as InputRecieving2     from           select id message d   from sysssislog cross apply                    SELECT Item   y i value     text    1     varchar 200                   row number   over order by y i  as rn           FROM                            SELECT x   CONVERT XML    lt i gt     REPLACE Message         lt  i gt  lt i gt        lt  i gt    query                  AS a CROSS APPLY x nodes  i   AS y i           d        WHERE event                   OnPipelineRowsSent        as tokens      pivot        max item  for  rn  in   3   4   5   6   7   8   9   10          as data   ran in 8 30  select id  tokens value    n 3      varchar 100   as PathProvidingID  tokens value    n 4      varchar 100    as PathProvider  tokens value    n 5      varchar 100    as ComponentProvidingID  tokens value    n 6      varchar 100    as ComponentProviding  tokens value    n 7      varchar 100    as InputRecievingID  tokens value    n 8      varchar 100    as InputRecieving  tokens value    n 9      varchar 100    as RowsPassed  from       select id  Convert xml   lt n gt   Replace message       lt  n gt  lt n gt      lt  n gt    tokens          from sysssislog         WHERE event                   OnPipelineRowsSent        as data   ran in 9 20

User · Answer

I ve been using vzczc s answer using recursive cte s for some time  but have wanted to update it to handle a variable length separator and also to handle strings with leading and lagging  separators  such as when you have a csv file with records such as    Bob   Smith   Sunnyvale   CA   or when you are dealing with six part fqn s as shown below  I use these extensively for logging of the subject fqn for auditing  error handling  etc  and parsename only handles four parts    netbios name   machine name   instance   database   schema   table   column    Here is my updated version  and thanks to vzczc s for his original post   select   from  utility   split string  N  this   string   gets   split   and   removes   leading   and   trailing   quotes    N       N     N       select   from  utility   split string  N  this   string   gets   split   but   leaves   leading   and   trailing   quotes    N       null  null    select   from  utility   split string  N  netbios name   machine name   instance   database   schema   table   column    N       N     N       create function  utility   split string        input        nvarchar  max        separator  sysname        lead       sysname        lag        sysname    returns  node list table       index    int        node   nvarchar  max      begin        declare  separator length  int   len  separator                    lead length     int    isnull len  lead   0                    lag length      int    isnull len  lag   0          --        set  input   right  input  len  input  -  lead length          set  input   left  input  len  input  -  lag length          --        with  splitter   index    starting position    start location               as  select cast  separator length as  bigint                            cast 1 as  bigint                            charindex  separator   input                  union all                 select  index    1                           start location     separator length                          charindex  separator   input   start location     separator length                  from    splitter                  where   start location   gt  0         --        insert into  node list                      index   node            select  index  -  separator length                   as  index                    substring  input   starting position   case                                                              when  start location   gt  0                                                                  then                                                                 start location  -  starting position                                                               else                                                                len  input                                                           end  as  node           from    splitter          --        return     end   go

User · Answer

You may find the solution in SQL User Defined Function to Parse a Delimited String helpful  from The Code Project    You can use this simple logic   Declare  products varchar 200     1 20 3 343 44 6 8765  Declare  individual varchar 20    null  WHILE LEN  products   gt  0 BEGIN     IF PATINDEX         products   gt  0     BEGIN         SET  individual   SUBSTRING  products                                      0                                      PATINDEX         products           SELECT  individual          SET  products   SUBSTRING  products                                    LEN  individual          1                                    LEN  products       END     ELSE     BEGIN         SET  individual    products         SET  products   NULL         SELECT  individual     END END

User · Answer

Starting with SQL Server 2016 we string split  DECLARE  string varchar 100     Richard  Mike  Mark   SELECT value FROM string split  string

User · Answer

SIMPLE SOLUTION FOR PARSING FIRST AND LAST NAME  DECLARE  Name varchar 10     John Smith   -- Get First Name SELECT SUBSTRING  Name  0   SELECT CHARINDEX       Name     -- Get Last Name SELECT SUBSTRING  Name   SELECT CHARINDEX       Name     1  LEN  Name     In my case  and in many others it seems      I have a list of first and last names separated by a single space   This can be used directly inside a select statement to parse first and last name   -- i e  Get First and Last Name from a table of Full Names SELECT SUBSTRING FullName  0   SELECT CHARINDEX      FullName    as FirstName  SUBSTRING FullName   SELECT CHARINDEX      FullName     1  LEN FullName   as LastName  From FullNameTable

User · Answer

Most of the solutions here use while loops or recursive CTEs  A set-based approach will be superior  I promise  if you can use a delimiter other than a space   CREATE FUNCTION  dbo   SplitString                 List NVARCHAR MAX            Delim VARCHAR 255            RETURNS TABLE     AS         RETURN   SELECT  Value   idx   RANK   OVER  ORDER BY n  FROM                           SELECT n   Number                  Value    LTRIM RTRIM SUBSTRING  List   Number                 CHARINDEX  Delim   List    Delim   Number   -  Number                 FROM  SELECT Number   ROW NUMBER   OVER  ORDER BY name                FROM sys all objects  AS x               WHERE Number  lt   LEN  List                AND SUBSTRING  Delim    List   Number   LEN  Delim      Delim             AS y              Sample usage   SELECT Value FROM dbo SplitString  foo bar blat foo splunge         WHERE idx   3    Results   ---- blat   You could also add the idx you want as an argument to the function  but I ll leave that as an exercise to the reader   You can t do this with just the native STRING SPLIT function added in SQL Server 2016  because there is no guarantee that the output will be rendered in the order of the original list  In other words  if you pass in 3 6 1 the result will likely be in that order  but it could be 1 3 6  I have asked for the community s help in improving the built-in function here    Please help with STRING SPLIT improvements    With enough qualitative feedback  they may actually consider making some of these enhancements    STRING SPLIT is not feature complete   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    Split strings the right way     or the next best way Splitting Strings   A Follow-Up Splitting Strings   Now with less T-SQL Comparing string splitting   concatenation methods Processing a list of integers   my approach Splitting a list of integers   another roundup More on splitting lists   custom delimiters  preventing duplicates  and maintaining order Removing Duplicates from Strings in SQL Server   On SQL Server 2016 or above  though  you should look at STRING SPLIT   and STRING AGG      Performance Surprises and Assumptions   STRING SPLIT   STRING SPLIT   in SQL Server 2016   Follow-Up  1 STRING SPLIT   in SQL Server 2016   Follow-Up  2 SQL Server v Next   STRING AGG   performance Solve old problems with SQL Server   s new STRING AGG and STRING SPLIT functions

User · Answer

Using SQL Server 2016 and above  Use this code to TRIM strings  ignore NULL values and apply a row index in the correct order  It also works with a space delimiter   DECLARE  STRING VALUE NVARCHAR MAX     one  two  three  four      five   SELECT ROW NUMBER   OVER  ORDER BY R  index    index   R  value  FROM       SELECT         1  index   NULLIF TRIM  value         value  FROM STRING SPLIT  STRING VALUE       T     WHERE         NULLIF TRIM  value        IS NOT NULL   R

User · Answer

Here is a SQL UDF that can split a string and grab just a certain piece   create FUNCTION  dbo   udf SplitParseOut         List nvarchar MAX        SplitOn nvarchar 5        GetIndex smallint     returns varchar 1000  AS    BEGIN  DECLARE  RtnValue table         Id int identity 0 1       Value nvarchar MAX           DECLARE  result varchar 1000       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        select  result   value from  RtnValue where ID    GetIndex      Return  result END

User · Answer

declare  strng varchar max   hello john smith  select       substring           strng          charindex       strng    1                       charindex       strng  charindex       strng    1             - charindex      strng

User · Answer

Try this   CREATE function  SplitWordList      list varchar 8000    returns  t table     Word varchar 50  not null   Position int identity 1 1  not null   as begin   declare       pos int       lpos int       item varchar 100        ignore varchar 100        dl int       a1 int       a2 int       z1 int       z2 int       n1 int       n2 int       c varchar 1        a smallint   select       a1   ascii  a         a2   ascii  A         z1   ascii  z         z2   ascii  Z         n1   ascii  0         n2   ascii  9     set  ignore           set  pos   1   set  dl   datalength  list    set  lpos   1   set  item        while   pos  lt    dl  begin     set  c   substring  list   pos  1      if   ignore not like        c        begin       set  a   ascii  c        if    a  gt    a1  and   a  lt    z1             or    a  gt    a2  and   a  lt    z2           or    a  gt    n1  and   a  lt    n2         begin         set  item    item    c       end else if   item  gt      begin         insert into  t values   item          set  item            end     end      set  pos    pos   1   end   if   item  gt      begin     insert into  t values   item    end   return end   Test it like this   select   from SplitWordList  Hello John Smith

User · Answer

Yet another get n th part of string by delimeter function   create function GetStringPartByDelimeter        value as nvarchar max        delimeter as nvarchar max        position as int   returns NVARCHAR MAX   AS BEGIN     declare  startPos as int     declare  endPos as int     set  endPos   -1     while   position  gt  0 and  endPos    0  begin         set  startPos    endPos   1         set  endPos   charindex  delimeter   value   startPos           if  position   1  begin             if  endPos   0                  set  endPos   len  value    1              return substring  value   startPos   endPos -  startPos          end          set  position    position - 1     end      return null end   and the usage   select dbo GetStringPartByDelimeter   a b c d e        3    which returns   c

User · Answer

This question is not about a string split approach  but about how to get the nth element  All answers here are doing some kind of string splitting using recursion  CTEs  multiple CHARINDEX  REVERSE and PATINDEX  inventing functions  call for CLR methods  number tables  CROSS APPLYs     Most answers cover many lines of code  But - if you really want nothing more than an approach to get the nth element - this can be done as real one-liner  no UDF  not even a sub-select    And as an extra benefit  type safe Get part 2 delimited by a space  DECLARE  input NVARCHAR 100  N part1 part2 part3   SELECT CAST N  lt x gt     REPLACE  input N    N  lt  x gt  lt x gt      N  lt  x gt   AS XML  value   x 2    nvarchar max     Of course you can use variables for delimiter and position  use sql column to retrieve the position directly from a query s value   DECLARE  dlmt NVARCHAR 10  N     DECLARE  pos INT   2  SELECT CAST N  lt x gt     REPLACE  input  dlmt N  lt  x gt  lt x gt      N  lt  x gt   AS XML  value   x sql variable  quot  pos quot    1    nvarchar max     If your string might include forbidden characters  especially one among  amp  gt  lt    you still can do it this way  Just use FOR XML PATH on your string first to replace all forbidden characters with the fitting escape sequence implicitly  It s a very special case if - additionally - your delimiter is the semicolon  In this case I replace the delimiter first to   DLMT    and replace this to the XML tags finally  SET  input N Some  lt    gt  and  amp  Other            One more   SET  dlmt N     SELECT CAST N  lt x gt     REPLACE  SELECT REPLACE  input  dlmt   DLMT    AS     FOR XML PATH      N  DLMT   N  lt  x gt  lt x gt      N  lt  x gt   AS XML  value   x sql variable  quot  pos quot    1    nvarchar max      UPDATE for SQL-Server 2016  Regretfully the developers forgot to return the part s index with STRING SPLIT  But  using SQL-Server 2016   there is JSON VALUE and OPENJSON  With JSON VALUE we can pass in the position as the index  array  For OPENJSON the documentation states clearly   When OPENJSON parses a JSON array  the function returns the indexes of the elements in the JSON text as keys   A string like 1 2 3 needs nothing more than brackets   1 2 3   A string of words like this is an example needs to be   quot this quot   quot is quot   quot an quot   quot example quot    These are very easy string operations  Just try it out  DECLARE  str VARCHAR 100   Hello John Smith   DECLARE  position INT   2   --We can build the json-path    1   using CONCAT SELECT JSON VALUE    quot     REPLACE  str       quot   quot        quot    CONCAT       position-1         --See this for a position safe string-splitter  zero-based   SELECT  JsonArray  key  AS  Position          JsonArray  value  AS  Part  FROM OPENJSON    quot     REPLACE  str       quot   quot        quot     JsonArray  In this post I tested various approaches and found  that OPENJSON is really fast  Even much faster than the famous  quot delimitedSplit8k   quot  method    UPDATE 2 - Get the values type-safe We can use an array within an array simply by using doubled       This allows for a typed WITH-clause  DECLARE   SomeDelimitedString VARCHAR 100   part1 1 20190920    DECLARE  JsonArray NVARCHAR MAX  CONCAT     quot   REPLACE  SomeDelimitedString       quot   quot      quot        SELECT  SomeDelimitedString          AS TheOriginal         JsonArray                    AS TransformedToJSON        ValuesFromTheArray   FROM OPENJSON  JsonArray  WITH TheFirstFragment  VARCHAR 100     0        TheSecondFragment INT             1        TheThirdFragment  DATE            2    ValuesFromTheArray

User · Answer

Here is a function that will accomplish the question s goal of splitting a string and accessing item X   CREATE FUNCTION  dbo   SplitString        List       VARCHAR MAX       Delimiter  VARCHAR 255       ElementNumber INT   RETURNS VARCHAR MAX  AS BEGIN         DECLARE  inp VARCHAR MAX         SET  inp    SELECT REPLACE  List  Delimiter   DELMTR    FOR XML PATH              DECLARE  xml XML        SET  xml     lt split gt  lt el gt     REPLACE  inp   DELMTR     lt  el gt  lt el gt        lt  el gt  lt  split gt           DECLARE  ret VARCHAR MAX         SET  ret    SELECT               el   split el value      varchar max           FROM   xml nodes   split el string-length    gt 0  position     sql variable   elementnumber      split el           RETURN  ret  END   Usage   SELECT dbo SplitString  Hello John Smith        2    Result   John

User · Answer

I devoloped this   declare  x nvarchar Max     ali veli deli    declare  item nvarchar Max   declare  splitter char       while CHARINDEX  splitter  x     0 begin     set  item   LEFT  x CHARINDEX  splitter  x       set  x      RIGHT  x len  x -len  item         select  item as item   x as x  end   the only attention you should is dot     that end of the  x is always should be there

User · Answer

Here I post a simple way of solution  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  Hello John Smith

User · Answer

In my opinion you guys are making it way too complicated  Just create a CLR UDF and be done with it   using System  using System Data  using System Data SqlClient  using System Data SqlTypes  using Microsoft SqlServer Server  using System Collections Generic   public partial class UserDefinedFunctions      SqlFunction    public static SqlString SearchString string Search        List lt string gt  SearchWords   new List lt string gt         foreach  string s in Search Split new char                     if   s ToLower   Equals  or    amp  amp   s ToLower   Equals  and              SearchWords Add s                      return new SqlString string Join   OR    SearchWords ToArray

User · Answer

I know it s an old Question  but i think some one can benefit from my solution   select  SUBSTRING column name 1 CHARINDEX     column name 1 -1   SUBSTRING SUBSTRING column name CHARINDEX     column name 1  1 LEN column name        1      CHARINDEX     SUBSTRING column name CHARINDEX     column name 1  1 LEN column name   1 -1   SUBSTRING SUBSTRING column name CHARINDEX     column name 1  1 LEN column name        CHARINDEX     SUBSTRING column name CHARINDEX     column name 1  1 LEN column name   1  1      LEN column name   from table name   SQL FIDDLE  Advantages    It separates all the 3 sub-strings deliminator by      One must not use while loop  as it decreases the performance  No need to Pivot as all the resultant sub-string will be displayed in one Row   Limitations    One must know the total no  of spaces  sub-string     Note  the solution can give sub-string up to to N   To overcame the limitation we can use the following ref   But again the above solution can t be use in a table  Actaully i wasn t able to use it    Again i hope this solution can help some-one   Update  In case of Records   50000 it is not advisable to use LOOPS as it will degrade the Performance

User · Answer

A simple optimized algorithm    ALTER FUNCTION  dbo   Split    Text NVARCHAR 200   Splitor CHAR 1    RETURNS  Result TABLE   value NVARCHAR 50    AS BEGIN     DECLARE  PathInd INT     Set  Text   Splitor     WHILE LEN  Text   gt  0     BEGIN         SET  PathInd PATINDEX      Splitor      Text          INSERT INTO   Result VALUES SUBSTRING  Text  0   PathInd           SET  Text  SUBSTRING  Text   PathInd 1  LEN  Text       END         RETURN  END

User · Answer

Aaron Bertrand s answer is great  but flawed  It doesn t accurately handle a space as a delimiter  as was the example in the original question  since the length function strips trailing spaces    The following is his code  with a small adjustment to allow for a space delimiter   CREATE FUNCTION  dbo   SplitString         List NVARCHAR MAX        Delim VARCHAR 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 objects  AS x           WHERE Number  lt   LEN  List            AND SUBSTRING  Delim    List   Number   LEN  Delim  x  -1     Delim         AS y

User · Answer

Pure set-based solution using TVF with recursive CTE  You can JOIN and APPLY this function to any dataset   create function  dbo   SplitStringToResultSet    value varchar max    separator char 1   returns table as return with r as       select value  cast null as varchar max    x   -1  no  from  select rtrim cast  value as varchar max     value   as j     union all     select right value  len value -case charindex  separator  value  when 0 then len value  else charindex  separator  value  end   value        left r  value   case charindex  separator  r value  when 0 then len r value  else abs charindex  separator  r  value  -1  end    x         no    1  no      from r where value  gt       select ltrim x   value    no   index  from r where x is not null  go   Usage   select   from  dbo   SplitStringToResultSet   Hello John Smith        where  index    1    Result   value   index ------------- John    1

User · Answer

Almost all the other answers are replacing the string being split which wastes CPU cycles and performs unnecessary memory allocations   I cover a much better way to do a string split here  http   www digitalruby com split-string-sql-server   Here is the code   SET NOCOUNT ON  -- You will want to change nvarchar MAX  to nvarchar 50   varchar 50  or whatever matches exactly with the string column you will be searching against DECLARE  SplitStringTable TABLE  Value nvarchar MAX  NOT NULL  DECLARE  StringToSplit nvarchar MAX     your string to split here  DECLARE  SplitEndPos int DECLARE  SplitValue nvarchar MAX  DECLARE  SplitDelim nvarchar 1        DECLARE  SplitStartPos int   1  SET  SplitEndPos   CHARINDEX  SplitDelim   StringToSplit   SplitStartPos   WHILE  SplitEndPos  gt  0 BEGIN     SET  SplitValue   SUBSTRING  StringToSplit   SplitStartPos    SplitEndPos -  SplitStartPos       INSERT  SplitStringTable  Value  VALUES   SplitValue      SET  SplitStartPos    SplitEndPos   1     SET  SplitEndPos   CHARINDEX  SplitDelim   StringToSplit   SplitStartPos  END  SET  SplitValue   SUBSTRING  StringToSplit   SplitStartPos  2147483647  INSERT  SplitStringTable  Value  VALUES  SplitValue   SET NOCOUNT OFF  -- You can select or join with the values in  SplitStringTable at this point

User · Answer

This pattern works fine and you can generalize  Convert xml   lt n gt   Replace FIELD       lt  n gt  lt n gt      lt  n gt    value    n INDEX     TYPE                                                                                      note FIELD  INDEX and TYPE   Let some table with identifiers like  sys message 1234 warning A45 sys message 1235 error O98        Then  you can write  SELECT Source           q value    n 1      varchar 10            RecordType       q value    n 2      varchar 20            RecordNumber     q value    n 3      int           Status           q value    n 4      varchar 5    FROM              SELECT   q   Convert xml   lt n gt   Replace fieldName       lt  n gt  lt n gt      lt  n gt             FROM     some TABLE          Q   splitting and casting all parts

User · Answer

Here is a UDF which will do it   It will return a table of the delimited values  haven t tried all scenarios on it but your example works fine    CREATE FUNCTION SplitString        -- Add the parameters for the function here      myString varchar 500        deliminator varchar 10    RETURNS   ReturnTable TABLE        -- Add the column definitions for the TABLE variable here      id   int  IDENTITY 1 1  NOT NULL       part   varchar  50  NULL   AS BEGIN         Declare  iSpaces int         Declare  part varchar 50           --initialize spaces         Select  iSpaces   charindex  deliminator  myString 0          While  iSpaces   0          Begin             Select  part   substring  myString 0 charindex  deliminator  myString 0                Insert Into  ReturnTable part              Select  part      Select  myString   substring  mystring charindex  deliminator  myString 0   len  deliminator  len  myString  - charindex      myString 0                 Select  iSpaces   charindex  deliminator  myString 0          end          If len  myString    0             Insert Into  ReturnTable             Select  myString      RETURN  END GO   You would call it like this    Select   From SplitString  Hello John Smith         Edit   Updated solution to handle delimters with a len 1 as in     select   From SplitString  Hello  John  Smith

User · Answer

Recursive CTE solution with server pain  test it  MS SQL Server 2008 Schema Setup   create table Course  Courses varchar 100     insert into Course values   Hello John Smith      Query 1   with cte as      select          left  Courses  charindex        Courses    as a l          cast  substring  Courses                            charindex        Courses    1                             len Courses                          as varchar 100     as a r          Courses as a          0 as n      from Course t     union all       select          left a r  charindex        a r    as a l          substring  a r  charindex        a r    1   len a R     as a r          cte a          cte n   1 as n     from Course t inner join cte           on t Courses   cte a and len  a r    gt  0       select a l  n from cte --where N   1   Results        A L   N    -------- ---    Hello    0      John    1     Smith    2

User · Answer

Well  mine isn t all that simpler  but here is the code I use to split a comma-delimited input variable into individual values  and put it into a table variable  I m sure you could modify this slightly to split based on a space and then to do a basic SELECT query against that table variable to get your results   -- Create temporary table to parse the list of accounting cycles  DECLARE  tblAccountingCycles table       AccountingCycle varchar 10     DECLARE  vchAccountingCycle varchar 10  DECLARE  intPosition int  SET  vchAccountingCycleIDs   LTRIM RTRIM  vchAccountingCycleIDs         SET  intPosition   CHARINDEX       vchAccountingCycleIDs  1   IF REPLACE  vchAccountingCycleIDs            lt  gt     BEGIN     WHILE  intPosition  gt  0     BEGIN         SET  vchAccountingCycle   LTRIM RTRIM LEFT  vchAccountingCycleIDs   intPosition - 1            IF  vchAccountingCycle  lt  gt             BEGIN             INSERT INTO  tblAccountingCycles  AccountingCycle  VALUES   vchAccountingCycle          END         SET  vchAccountingCycleIDs   RIGHT  vchAccountingCycleIDs  LEN  vchAccountingCycleIDs  -  intPosition          SET  intPosition   CHARINDEX       vchAccountingCycleIDs  1      END END   The concept is pretty much the same  One other alternative is to leverage the  NET compatibility within SQL Server 2005 itself  You can essentially write yourself a simple method in  NET that would split the string and then expose that as a stored procedure function

User · Answer

CREATE FUNCTION  dbo   fnSplitString           string NVARCHAR MAX         delimiter CHAR 1      RETURNS  output TABLE splitdata NVARCHAR MAX      BEGIN      DECLARE  start INT   end INT      SELECT  start   1   end   CHARINDEX  delimiter   string       WHILE  start  lt  LEN  string    1 BEGIN          IF  end   0               SET  end   LEN  string    1          INSERT INTO  output  splitdata            VALUES SUBSTRING  string   start   end -  start            SET  start    end   1          SET  end   CHARINDEX  delimiter   string   start       END      RETURN  END   AND USE IT  select  from dbo fnSplitString  Querying SQL Server

User · Answer

If you check the following SQL tutorial on splitting string using SQL  you will find a number of functions that can be used to split a given string on SQL Server  For example  SplitAndReturnNth UDF function can be used to split a text using a separator and return the Nth piece as the output of the function  select dbo SplitAndReturnNth  Hello John Smith      2

User · Answer

Alter Function dbo fn Split            Expression nvarchar max        Delimiter  nvarchar 20              Qualifier  char 1    Null           RETURNS  Results TABLE  id int IDENTITY 1 1   value nvarchar max       AS     BEGIN           USAGE             Select   From dbo fn Split  apple pear grape banana orange honeydew cantalope 3 2 1 4        Null              Select   From dbo fn Split  1 abc  Doe  John  4                         Select   From dbo fn Split  Hello 0                                        -- Declare Variables        DECLARE            X     xml             Temp  nvarchar max              Temp2 nvarchar max              Start int             End   int         -- HTML Encode  Expression        Select  Expression    Select  Expression For XML Path              -- Find all occurences of  Delimiter within  Qualifier and replace with                  While PATINDEX        Qualifier         Expression    0 AND Len IsNull  Qualifier         0        BEGIN           Select              -- Starting character position of  Qualifier               Start   PATINDEX        Qualifier         Expression                --  Expression starting at the  Start position               Temp   SubString  Expression   Start   1  LEN  Expression - Start 1                -- Next position of  Qualifier within  Expression               End   PATINDEX        Qualifier         Temp  - 1               -- The part of Expression found between the  Qualifiers               Temp2   Case When  End  LT 0 Then  Temp Else Left  Temp   End  End               -- New  Expression               Expression   REPLACE  Expression                                      Qualifier    Temp2   Case When  End  LT 0 Then    Else  Qualifier End                                     Replace  Temp2   Delimiter                                                   END         -- Replace all occurences of  Delimiter within  Expression with   lt fn Split gt ltfn Split gt         -- And convert it to XML so we can select from it        SET            X   Cast   ltfn Split gt                        Replace  Expression   Delimiter    lt fn Split gt ltfn Split gt                           lt fn Split gt  as xml          -- Insert into our returnable table replacing             back to  Delimiter        INSERT  Results        SELECT            Value    LTRIM RTrim Replace C value       nvarchar max                   Delimiter           FROM            X nodes  fn Split   as X C          -- Return our temp table        RETURN     END

User · Answer

I use the answer of frederic but this did not work in SQL Server 2005  I modified it and I m using select with union all and it works  DECLARE  str varchar max  SET  str    Hello John Smith how are you   DECLARE  separator varchar max  SET  separator        DECLARE  Splited table id int IDENTITY 1 1   item varchar max    SET  str   REPLACE  str   separator      UNION ALL SELECT      SET  str     SELECT         str            INSERT INTO  Splited EXEC  str   SELECT   FROM  Splited   And the result-set is   id  item 1   Hello 2   John 3   Smith 4   how 5   are 6   you

User · Answer

I don t believe SQL Server has a built-in split function  so other than a UDF  the only other answer I know is to hijack the PARSENAME function   SELECT PARSENAME REPLACE  Hello John Smith              2     PARSENAME takes a string and splits it on the period character   It takes a number as its second argument  and that number specifies which segment of the string to return  working from back to front    SELECT PARSENAME REPLACE  Hello John Smith              3   --return Hello   Obvious problem is when the string already contains a period   I still think using a UDF is the best way   any other suggestions

User · Answer

First  create a function  using CTE  common table expression does away with the need for a temp table     create function dbo SplitString                 str nvarchar 4000             separator char 1            returns table     AS     return           with tokens p  a  b  AS               select                  1                   1                   charindex  separator   str              union all             select                 p   1                   b   1                   charindex  separator   str  b   1              from tokens             where b  gt  0                   select             p-1 zeroBasedOccurance              substring                   str                   a                   case when b  gt  0 then b-a ELSE 4000 end               AS s         from tokens             GO   Then  use it as any table  or modify it to fit within your existing stored proc  like this    select s  from dbo SplitString  Hello John Smith        where zeroBasedOccurance 1   Update  Previous version would fail for input string longer than 4000 chars  This version takes care of the limitation   create function dbo SplitString         str nvarchar max         separator char 1    returns table AS return   with tokens p  a  b  AS       select          cast 1 as bigint            cast 1 as bigint            charindex  separator   str      union all     select         p   1           b   1           charindex  separator   str  b   1      from tokens     where b  gt  0   select     p-1 ItemIndex      substring           str           a           case when b  gt  0 then b-a ELSE LEN  str  end       AS s from tokens     GO   Usage remains the same

User · Answer

building on  NothingsImpossible solution  or  rather  comment on the most voted answer  just below the accepted one   i found the following quick-and-dirty solution fulfill my own needs - it has a benefit of being solely within SQL domain   given a string  first second third fourth fifth   say  I want to get the third token  this works only if we know how many tokens the string is going to have - in this case it s 5  so my way of action is to chop the last two tokens away  inner query   and then to chop the first two tokens away  outer query   i know that this is ugly and covers the specific conditions i was in  but am posting it just in case somebody finds it useful  cheers  select      REVERSE          SUBSTRING              reverse substring               0               CHARINDEX      reverse substring                   from        select          msg          SUBSTRING              REVERSE msg                CHARINDEX                                        REVERSE msg                    CHARINDEX                                               REVERSE msg                    1               1              1000           reverse substring     from                select  first second third fourth fifth  msg       a   b

User · Answer

if anyone wants to get only one part of the seperatured text can use this  select   from fromSplitStringSep  Word1 wordr2 word3         CREATE function  dbo   SplitStringSep          str nvarchar 4000         separator char 1    returns table AS return       with tokens p  a  b  AS           select          1           1           charindex  separator   str          union all         select             p   1               b   1               charindex  separator   str  b   1          from tokens         where b  gt  0                   select             p-1 zeroBasedOccurance              substring                   str                   a                   case when b  gt  0 then b-a ELSE 4000 end               AS s         from tokens

User · Answer

I was looking for the solution on net and the below works for me  Ref   And you call the function like this    SELECT   FROM dbo split  ram shyam hari gopal           SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO  CREATE FUNCTION  dbo   Split   String VARCHAR 8000    Delimiter CHAR 1          RETURNS  temptable TABLE  items VARCHAR 8000          AS        BEGIN            DECLARE  idx INT            DECLARE  slice VARCHAR 8000              SELECT  idx   1            IF len  String  lt 1 OR  String IS NULL  RETURN            WHILE  idx   0            BEGIN                SET  idx   charindex  Delimiter  String                 IF  idx  0                    SET  slice   LEFT  String  idx - 1                 ELSE                    SET  slice    String                IF len  slice  gt 0                INSERT INTO  temptable Items  VALUES  slice                 SET  String   RIGHT  String len  String  -  idx                 IF len  String    0 break            END        RETURN        END

User · Answer

You can leverage a Number table to do the string parsing    Create a physical numbers table       create table dbo Numbers  N int primary key       insert into dbo Numbers         select top 1000 row number   over order by number  from master  spt values     go   Create test table with 1000000 rows      create table  yak  i int identity 1 1  primary key  array varchar 50        insert into  yak array          select  a b c  from dbo Numbers n cross join dbo Numbers nn     go   Create the function      create function  dbo   ufn ParseArray               Input      nvarchar 4000                 Delimiter  char 1                      BaseIdent  int               returns table as     return               select  row number   over  order by n asc      BaseIdent - 1   i                       substring  Input  n  charindex  Delimiter   Input    Delimiter  n  - n  s             from    dbo Numbers             where   n  lt   convert int  len  Input   and                     substring  Delimiter    Input  n  1     Delimiter               go   Usage  outputs 3mil rows in 40s on my laptop       select        from  yak      cross apply dbo ufn ParseArray array       1    cleanup      drop table dbo Numbers      drop function   dbo   ufn ParseArray    Performance here is not amazing  but calling a function over a million row table is not the best idea  If performing a string split over many rows I would avoid the function

User · Answer

I know its late  but I recently had this requirement and came up with the below code  I don t have a choice to use User defined function  Hope this helps   SELECT      SUBSTRING                  SUBSTRING  Hello John Smith   0 CHARINDEX      Hello John Smith  CHARINDEX      Hello John Smith   1                            CHARINDEX      Hello John Smith   LEN  Hello John Smith

[sql] How do I split a string so I can access item x?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to split