T-SQL split string based on delimiter

Question

I have some data that I would like to split based on a delimiter that may or may not exist  Example data  John Smith Jane Doe Steve Bob Johnson  I am using the following code to split this data into First and Last names  SELECT SUBSTRING myColumn  1  CHARINDEX      myColumn -1  AS FirstName         SUBSTRING myColumn  CHARINDEX      myColumn    1  1000  AS LastName FROM   MyTable  The results I would like  FirstName---LastName John--------Smith Jane--------Doe Steve-------NULL Bob---------Johnson  This code works just fine as long as all the rows have the anticipated delimiter  but errors out when a row does not   quot Invalid length parameter passed to the LEFT or SUBSTRING function  quot   How can I re-write this to work properly

User · Answer

Try filtering out the rows that contain strings with the delimiter and work on those only like:

SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName,
       SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM   MyTable
WHERE CHARINDEX('/', myColumn) > 0

Or

SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName,
       SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM   MyTable
WHERE myColumn LIKE '%/%'

User · Answer

I just wanted to give an alternative way to split a string with multiple delimiters  in case you are using a SQL Server version under 2016   The general idea is to split out all of the characters in the string  determine the position of the delimiters  then obtain substrings relative to the delimiters  Here is a sample    -- Sample data DECLARE  testTable TABLE       TestString      VARCHAR 50    INSERT INTO  testTable VALUES        Teststring 1 2 3          Test    DECLARE  delimiter VARCHAR 1         -- Generate numbers with which we can enumerate  WITH Numbers AS       SELECT 1 AS N      UNION ALL       SELECT N   1     FROM Numbers      WHERE N  lt  255     -- Enumerate letters in the string and select only the delimiters Letters AS       SELECT  n N               SUBSTRING t TestString  n N  1  AS Letter               t TestString                ROW NUMBER   OVER     PARTITION BY t TestString                                     ORDER BY n N                                   AS Delimiter Number      FROM Numbers n         INNER JOIN  testTable t             ON n  lt   LEN t TestString      WHERE SUBSTRING t TestString  n  1     delimiter       UNION       -- Include 0th position to  delimit  the start of the string     SELECT  0               NULL               t TestString                0     FROM  testTable t    -- Obtain substrings based on delimiter positions SELECT  t TestString            ds Delimiter Number   1 AS Position           SUBSTRING t TestString  ds N   1  ISNULL de N  LEN t TestString    1  - ds N - 1  AS Delimited Substring  FROM  testTable t     LEFT JOIN Letters ds         ON t TestString   ds TestString      LEFT JOIN Letters de         ON t TestString   de TestString          AND ds Delimiter Number   1   de Delimiter Number   OPTION  MAXRECURSION 0

User · Answer

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

User · Answer

SELECT CASE          WHEN CHARINDEX      myColumn  0    0             THEN myColumn         ELSE LEFT myColumn  CHARINDEX      myColumn  0 -1          END AS FirstName      CASE          WHEN CHARINDEX      myColumn  0    0             THEN            ELSE RIGHT myColumn  CHARINDEX      REVERSE myColumn   0 -1          END AS LastName FROM MyTable

User · Answer

For those looking for answers for SQL Server 2016   Use the built-in STRING SPLIT function   Eg   DECLARE  tags NVARCHAR 400     clothing road  touring bike     SELECT value   FROM STRING SPLIT  tags         WHERE RTRIM value   lt  gt          Reference  https   msdn microsoft com en-nz library mt684588 aspx

User · Answer

ALTER FUNCTION  dbo   split string              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

User · Answer

These all helped me get to this  I am still on 2012 but now have something quick that will allow me to split a string  even if string has varying numbers of delimiters  and grab the nth substring from that string  It s quick too  I know this post is old  but it took me forever to find something so hopefully this will help someone else    CREATE FUNCTION  dbo   SplitsByIndex    separator VARCHAR 20             string    VARCHAR MAX      position  INT   RETURNS VARCHAR MAX  AS      BEGIN      DECLARE  results TABLE       id   INT IDENTITY 1  1          chrs VARCHAR 8000               DECLARE  outResult VARCHAR 8000        WITH X N            AS  SELECT  Table1                FROM VALUES 0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0   T C              Y N            AS  SELECT  Table2                FROM X A1                      X A2                      X A3                      X A4                      X A5                      X A6                      X A7                      X A8   -- Up to 16 8   4 billion           T N            AS  SELECT TOP  ISNULL LEN  string   0   ROW NUMBER   OVER                                                     ORDER BY                                   SELECT NULL                  - 1 N               FROM Y             Delim Pos            AS  SELECT t N               FROM T               WHERE SUBSTRING  string  t N  LEN  separator    x   - 1  LIKE  separator                     OR t N   0              Separated value            AS  SELECT SUBSTRING  string  d Pos   LEN  separator    x   - 1  LEAD d Pos  1  2147483647  OVER                       ORDER BY                                   SELECT NULL                 -d Pos - LEN  separator                 FROM Delim d               WHERE  string IS NOT NULL            INSERT INTO  results chrs                   SELECT s value                  FROM Separated s                  WHERE s value  lt  gt   separator       SELECT  outResult                   SELECT chrs          FROM  results          WHERE id    position              RETURN  outResult   END    This can be used like this   SELECT  dbo   SplitsByIndex      fieldname 2   from tablename

User · Answer

The examples above work fine when there is only one delimiter  but it doesn t scale well for multiple delimiters  Note that this will only work for SQL Server 2016 and above     Some Sample Data   DECLARE  mytable TABLE   id  VARCHAR 10    name  VARCHAR 1000    INSERT INTO  mytable VALUES   1   John Smith     2   Jane Doe      3   Steve      4   Bob Johnson       Split based on delimeter   SELECT P id   1   FirstName    2   LastName    3   Col3    4   Col4  FROM      SELECT A id  X1 VALUE  ROW NUMBER   OVER  PARTITION BY A id ORDER BY A id  RN     FROM  mytable A     CROSS APPLY STRING SPLIT A name       X1       A PIVOT  MAX A  VALUE   FOR A RN IN   1   2   3   4   5    P

[sql] T-SQL split string based on delimiter

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to sql-server-2008-r2