How to split a comma-separated value to columns

Question

I have a table like this  Value   String ------------------- 1       Cleo  Smith   I want to separate the comma delimited string into two columns  Value  Name Surname ------------------- 1      Cleo   Smith   I need only two fixed extra columns

User · Answer

Select distinct PROJ UID PROJ NAME RES UID from E2E ProjectWiseTimesheetActuals where   CHARINDEX     cast PROJ UID as varchar 8000         params   gt  0 and  CHARINDEX     cast RES UID as varchar 8000         res   gt  0

User · Answer

DECLARE  INPUT VARCHAR  MAX   N A R E N D R A  DECLARE  ELIMINATE CHAR CHAR  1      DECLARE  L START INT 1 DECLARE  L END INT  SELECT LEN   INPUT   DECLARE  OUTPUT CHAR  1   WHILE  L START  lt   L END BEGIN     SET  OUTPUT  SUBSTRING   INPUT  L START 1       IF  OUTPUT   ELIMINATE CHAR     BEGIN         PRINT  OUTPUT     END     SET  L START  L START 1 END

User · Answer

Your purpose can be solved using following query -   Select Value    Substring FullName  1 Charindex      FullName -1  as Name  Substring FullName  Charindex      FullName  1  LEN FullName   as  Surname from Table1   There is no readymade Split function in sql server  so we need to create user defined function   CREATE FUNCTION Split          InputString                  VARCHAR 8000          Delimiter                    VARCHAR 50     RETURNS  Items TABLE         Item                          VARCHAR 8000     AS BEGIN       IF  Delimiter             BEGIN             SET  Delimiter                   SET  InputString   REPLACE  InputString        Delimiter        END        IF   Delimiter IS NULL OR  Delimiter                   SET  Delimiter        --INSERT INTO  Items VALUES   Delimiter  -- Diagnostic --INSERT INTO  Items VALUES   InputString  -- Diagnostic        DECLARE  Item           VARCHAR 8000        DECLARE  ItemList       VARCHAR 8000        DECLARE  DelimIndex     INT        SET  ItemList    InputString       SET  DelimIndex   CHARINDEX  Delimiter   ItemList  0        WHILE   DelimIndex    0        BEGIN             SET  Item   SUBSTRING  ItemList  0   DelimIndex              INSERT INTO  Items VALUES   Item               -- Set  ItemList    ItemList minus one less item             SET  ItemList   SUBSTRING  ItemList   DelimIndex 1  LEN  ItemList - DelimIndex              SET  DelimIndex   CHARINDEX  Delimiter   ItemList  0        END -- End WHILE        IF  Item IS NOT NULL -- At least one delimiter was encountered in  InputString       BEGIN             SET  Item    ItemList             INSERT INTO  Items VALUES   Item        END        -- No delimiters were encountered in  InputString  so just return  InputString       ELSE INSERT INTO  Items VALUES   InputString         RETURN  END -- End Function GO  ---- Set Permissions --GRANT SELECT ON Split TO UserRole1 --GRANT SELECT ON Split TO UserRole2 --GO

User · Answer

Try this   declare  csv varchar 100    aaa bb csda daass   set  csv    csv       with cte as       select SUBSTRING  csv 1 charindex      csv 1 -1  as val  SUBSTRING  csv charindex      csv 1  1 len  csv   as rem      UNION ALL     select SUBSTRING a rem 1 charindex     a rem 1 -1 as val  SUBSTRING a rem charindex     a rem 1  1 len A rem        from cte a where LEN a rem  gt  1       select val from cte

User · Answer

I think this is cool   SELECT value      PARSENAME REPLACE String          2   Name        PARSENAME REPLACE String          1   Sur Name  FROM table WITH  NOLOCK

User · Answer

There are multiple ways to solve this and many different ways have been proposed already  Simplest would be to use LEFT   SUBSTRING and other string functions to achieve the desired result   Sample Data  DECLARE  tbl1 TABLE  Value INT String VARCHAR MAX    INSERT INTO  tbl1 VALUES 1  Cleo  Smith    INSERT INTO  tbl1 VALUES 2  John  Mathew      Using String Functions like LEFT  SELECT     Value      LEFT String CHARINDEX     String -1  as Fname      LTRIM RIGHT String LEN String  - CHARINDEX     String     AS Lname FROM  tbl1   This approach fails if there are more 2 items in a String   In such a scenario  we can use a splitter and then use PIVOT or convert the string into an XML and use  nodes to get string items  XML based solution have been detailed out by aads and bvr in their solution    The answers for this question which use splitter  all use WHILE which is inefficient for splitting  Check this performance comparison  One of the best splitters around is DelimitedSplit8K  created by Jeff Moden  You can read more about it here  Splitter with PIVOT  DECLARE  tbl1 TABLE  Value INT String VARCHAR MAX    INSERT INTO  tbl1 VALUES 1  Cleo  Smith    INSERT INTO  tbl1 VALUES 2  John  Mathew      SELECT t3 Value  1  as Fname  2  as Lname FROM  tbl1 as t1 CROSS APPLY  dbo   DelimitedSplit8K  String      as t2 PIVOT MAX Item  FOR ItemNumber IN   1   2    as t3   Output  Value   Fname   Lname 1   Cleo    Smith 2   John    Mathew   DelimitedSplit8K by Jeff Moden  CREATE FUNCTION  dbo   DelimitedSplit8K                                                                                                                           Purpose   Split a given string at a given delimiter and return a list of the split elements  items     Notes   1   Leading a trailing delimiters are treated as if an empty string element were present   2   Consecutive delimiters are treated as if an empty string element were present between them   3   Except when spaces are used as a delimiter  all spaces present in each element are preserved    Returns   iTVF containing the following   ItemNumber   Element position of Item as a BIGINT  not converted to INT to eliminate a CAST   Item         Element value as a VARCHAR 8000    Statistics on this function may be found at the following URL   http   www sqlservercentral com Forums Topic1101315-203-4 aspx   CROSS APPLY Usage Examples and Tests  --                                                                                                                      -- TEST 1  -- This tests for various possible conditions in a string using a comma as the delimiter   The expected results are -- laid out in the comments --                                                                                                                      --      Conditionally drop the test tables to make reruns easier for testing       --  this is NOT a part of the solution       IF OBJECT ID  tempdb   JBMTest   IS NOT NULL DROP TABLE  JBMTest   --      Create and populate a test table on the fly  this is NOT a part of the solution        -- In the following comments   b  is a blank and  E  is an element in the left to right order       -- Double Quotes are used to encapsulate the output of  Item  so that you can see that all blanks      -- are preserved no matter where they may appear   SELECT      INTO  JBMTest    FROM                                                 --   amp  type of Return Row s           SELECT  0  NULL                      UNION ALL --1 NULL          SELECT  1  SPACE 0                   UNION ALL --1 b  Empty String           SELECT  2  SPACE 1                   UNION ALL --1 b  1 space           SELECT  3  SPACE 5                   UNION ALL --1 b  5 spaces           SELECT  4                            UNION ALL --2 b b  both are empty strings           SELECT  5   55555                    UNION ALL --1 E          SELECT  6    55555                   UNION ALL --2 b E          SELECT  7    55555                   UNION ALL --3 b E b          SELECT  8   55555                    UNION ALL --2 b B          SELECT  9   55555 1                  UNION ALL --2 E E          SELECT 10   1 55555                  UNION ALL --2 E E          SELECT 11   55555 4444 333 22 1      UNION ALL --5 E E E E E           SELECT 12   55555 4444  333 22 1     UNION ALL --6 E E b E E E          SELECT 13    55555 4444  333 22 1    UNION ALL --8 b E E b E E E b          SELECT 14    55555 4444   333 22 1   UNION ALL --9 b E E b b E E E b          SELECT 15    4444 55555              UNION ALL --2 E  w Leading Space  E  w Trailing Space           SELECT 16   This is a test                     --E E E E           d  SomeID  SomeValue    --      Split the CSV column for the whole table using CROSS APPLY  this is the solution   SELECT test SomeID  test SomeValue  split ItemNumber  Item   QUOTENAME split Item         FROM  JBMTest test   CROSS APPLY dbo DelimitedSplit8K test SomeValue      split   --                                                                                                                      -- TEST 2  -- This tests for various  alpha  splits and COLLATION using all ASCII characters from 0 to 255 as a delimiter against -- a given string   Note that not all of the delimiters will be visible and some will show up as tiny squares because -- they are  control  characters   More specifically  this test will show you what happens to various non-accented  -- letters for your given collation depending on the delimiter you chose  --                                                                                                                      WITH  cteBuildAllCharacters  String Delimiter  AS     SELECT TOP 256           ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789           CHAR ROW NUMBER   OVER  ORDER BY  SELECT NULL  -1     FROM master sys all columns    SELECT ASCII Value   ASCII c Delimiter   c Delimiter  split ItemNumber  Item   QUOTENAME split Item         FROM cteBuildAllCharacters c   CROSS APPLY dbo DelimitedSplit8K c String c Delimiter  split   ORDER BY ASCII Value  split ItemNumber   -----------------------------------------------------------------------------------------------------------------------  Other Notes   1  Optimized for VARCHAR 8000  or less   No testing or error reporting for truncation at 8000 characters is done   2  Optimized for single character delimiter   Multi-character delimiters should be resolvedexternally from this      function   3  Optimized for use with CROSS APPLY   4  Does not  trim  elements just in case leading or trailing blanks are intended   5  If you don t know how a Tally table can be used to replace loops  please see the following        http   www sqlservercentral com articles T-SQL 62867   6  Changing this function to use NVARCHAR MAX  will cause it to run twice as slow   It s just the nature of      VARCHAR MAX  whether it fits in-row or not   7  Multi-machine testing for the method of using UNPIVOT instead of 10 SELECT UNION ALLs shows that the UNPIVOT method     is quite machine dependent and can slow things down quite a bit  -----------------------------------------------------------------------------------------------------------------------  Credits   This code is the product of many people s efforts including but not limited to the following   cteTally concept originally by Iztek Ben Gan and  decimalized  by Lynn Pettis  and others  for a bit of extra speed  and finally redacted by Jeff Moden for a different slant on readability and compactness  Hat s off to Paul White for  his simple explanations of CROSS APPLY and for his detailed testing efforts  Last but not least  thanks to  Ron  BitBucket  McCullough and Wayne Sheffield for their extreme performance testing across multiple machines and  versions of SQL Server   The latest improvement brought an additional 15-20  improvement over Rev 05   Special thanks  to  Nadrek  and  peter-757102   aka Peter de Heer  for bringing such improvements to light   Nadrek s original  improvement brought about a 10  performance gain and Peter followed that up with the content of Rev 07      I also thank whoever wrote the first article I ever saw on  numbers tables  which is located at the following URL  and to Adam Machanic for leading me to it many years ago   http   sqlserver2000 databases aspfaq com why-should-i-consider-using-an-auxiliary-numbers-table html -----------------------------------------------------------------------------------------------------------------------  Revision History   Rev 00 - 20 Jan 2010 - Concept for inline cteTally  Lynn Pettis and others                          Redaction Implementation  Jeff Moden          - Base 10 redaction and reduction for CTE    Total rewrite    Rev 01 - 13 Mar 2010 - Jeff Moden         - Removed one additional concatenation and one subtraction from the SUBSTRING in the SELECT List for that tiny           bit of extra speed    Rev 02 - 14 Apr 2010 - Jeff Moden         - No code changes   Added CROSS APPLY usage example to the header  some additional credits  and extra            documentation    Rev 03 - 18 Apr 2010 - Jeff Moden         - No code changes   Added notes 7  8  and 9 about certain  optimizations  that don t actually work for this           type of function    Rev 04 - 29 Jun 2010 - Jeff Moden         - Added WITH SCHEMABINDING thanks to a note by Paul White   This prevents an unnecessary  Table Spool  when the           function is used in an UPDATE statement even though the function makes no external references    Rev 05 - 02 Apr 2011 - Jeff Moden         - Rewritten for extreme performance improvement especially for larger strings approaching the 8K boundary and           for strings that have wider elements   The redaction of this code involved removing ALL concatenation of            delimiters  optimization of the maximum  N  value by using TOP instead of including it in the WHERE clause            and the reduction of all previous calculations  thanks to the switch to a  zero based  cteTally  to just one            instance of one add and one instance of a subtract  The length calculation for the final element  not            followed by a delimiter  in the string to be split has been greatly simplified by using the ISNULL NULLIF            combination to determine when the CHARINDEX returned a 0 which indicates there are no more delimiters to be           had or to start with  Depending on the width of the elements  this code is between 4 and 8 times faster on a           single CPU box than the original code especially near the 8K boundary          - Modified comments to include more sanity checks on the usage example  etc          - Removed  other  notes 8 and 9 as they were no longer applicable    Rev 06 - 12 Apr 2011 - Jeff Moden         - Based on a suggestion by Ron  Bitbucket  McCullough  additional test rows were added to the sample code and           the code was changed to encapsulate the output in pipes so that spaces and empty strings could be perceived            in the output   The first  Notes  section was added   Finally  an extra test was added to the comments above    Rev 07 - 06 May 2011 - Peter de Heer  a further 15-20  performance enhancement has been discovered and incorporated            into this code which also eliminated the need for a  zero  position in the cteTally table                                                                                                                           --      Define I O parameters           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    GO

User · Answer

Try this out  CREATE FUNCTION  dbo   Split          ListOfValues varchar max        ValueSeparator varchar 10        RETURNS  ListOfValuesInRows TABLE        Value varchar max        AS   BEGIN     IF Len  ListOfValues    0     RETURN      if  ValueSeparator  lt  gt         Begin     WHILE CHARINDEX  ValueSeparator   ListOfValues   gt  0     BEGIN       INSERT INTO  ListOfValuesInRows       SELECT LTRIM RTRIM SUBSTRING  ListOfValues  1  CHARINDEX  ValueSeparator   ListOfValues -1          SET  ListOfValues   SubString  ListOfValues  CharIndex  ValueSeparator   ListOfValues  Len  ValueSeparator   Len  ListOfValues        END      INSERT INTO  ListOfValuesInRows     SELECT LTRIM RTRIM  ListOfValues      End    Else    BEGIN     DECLARE  xml XML      SET  xml   N  lt t gt     REPLACE  ListOfValues   ValueSeparator    lt  t gt  lt t gt        lt  t gt        INSERT INTO  ListOfValuesInRows  Value      SELECT LTRIM RTRIM r value        varchar MAX       AS item     FROM  xml nodes    t    AS records  r       END    RETURN    END

User · Answer

ALTER FUNCTION  dbo   StringListTo    StringList Nvarchar max   Separators char 1   start int   index int   RETURNS nvarchar max  AS BEGIN declare  out Nvarchar max  declare  i int declare  start old int set  start  start 1 set  i 1 while  i lt   index  begin     set  start old  start     set  start CHARINDEX      StringList  start 1      if   start gt 0      begin         set  out Substring  StringList  start old 1  start- start old-1      end else begin     set  out Substring  StringList  start old 1 len  StringList -1  end set  i  i 1 end RETURN  out END

User · Answer

CREATE FUNCTION  dbo   fnSplit   sInputList VARCHAR 8000    sDelimiter VARCHAR 8000         RETURNS  List TABLE  item VARCHAR 8000   BEGIN      DECLARE  sItem VARCHAR 8000      WHILE CHARINDEX  sDelimiter   sInputList  0   lt  gt  0     BEGIN          SELECT  sItem   RTRIM LTRIM SUBSTRING  sInputList  1  CHARINDEX  sDelimiter   sInputList 0  - 1                     sInputList   RTRIM LTRIM SUBSTRING  sInputList  CHARINDEX  sDelimiter   sInputList  0    LEN  sDelimiter  LEN  sInputList              -- Indexes to keep the position of searching         IF LEN  sItem   gt  0          INSERT INTO  List SELECT  sItem      END      IF LEN  sInputList   gt  0     BEGIN          INSERT INTO  List SELECT  sInputList -- Put the last item in      END      RETURN  END

User · Answer

I encountered a similar problem but a complex one and since this is the first thread i found regarding that issue i decided to post my finding  i know it is complex solution to a simple problem but i hope that i could help other people who go to this thread looking for a more complex solution  i had to split a string containing 5 numbers  column name  levelsFeed  and to show each number in a separate column  for example  8 1 2 2 2 should be shown as     1  2  3  4  5 ------------- 8  1  2  2  2   Solution 1  using XML functions  this solution for the slowest solution by far  SELECT Distinct FeedbackID     S a value    H r  1     INT   AS level1   S a value    H r  2     INT   AS level2   S a value    H r  3     INT   AS level3   S a value    H r  4     INT   AS level4   S a value    H r  5     INT   AS level5 FROM                   SELECT   CAST  N  lt H gt  lt r gt     REPLACE levelsFeed         lt  r gt  lt r gt         lt  r gt   lt  H gt   AS XML  AS  vals      FROM Feedbacks     as d CROSS APPLY d  vals  nodes   H r   S a    Solution 2  using Split function and pivot   the split function split a string to rows with the column name Data   SELECT FeedbackID   1   2   3   4   5  FROM       SELECT    ROW NUMBER   OVER  PARTITION BY feedbackID ORDER BY  SELECT  null   as rn  FROM       SELECT FeedbackID  levelsFeed     FROM Feedbacks    as a CROSS APPLY dbo Split levelsFeed         as SourceTable PIVOT       MAX data      FOR rn IN   1   2   3   4   5    as pivotTable   Solution 3  using string manipulations functions - fastest by small margin over solution 2  SELECT FeedbackID  SUBSTRING levelsFeed 0 CHARINDEX     levelsFeed   AS level1  PARSENAME REPLACE SUBSTRING levelsFeed CHARINDEX     levelsFeed  1 LEN levelsFeed            4  AS level2  PARSENAME REPLACE SUBSTRING levelsFeed CHARINDEX     levelsFeed  1 LEN levelsFeed            3  AS level3  PARSENAME REPLACE SUBSTRING levelsFeed CHARINDEX     levelsFeed  1 LEN levelsFeed            2  AS level4  PARSENAME REPLACE SUBSTRING levelsFeed CHARINDEX     levelsFeed  1 LEN levelsFeed            1  AS level5 FROM Feedbacks   since the levelsFeed contains 5 string values i needed to use the substring function for the first string   i hope that my solution will help other that got to this thread looking for a more complex split to columns methods

User · Answer

This worked for me  CREATE FUNCTION  dbo   SplitString        delimited NVARCHAR MAX        delimiter NVARCHAR 100    RETURNS  t TABLE   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

You can use a table-valued function STRING SPLIT  which is available only under compatibility level 130  If your database compatibility level is lower than 130  SQL Server will not be able to find and execute the STRING SPLIT function  You can change a compatibility level of the database using the following command   ALTER DATABASE DatabaseName SET COMPATIBILITY LEVEL   130   Syntax  SELECT   FROM STRING SPLIT   string  separator     see documentation here

User · Answer

WITH Split Names  Value Name  xmlname  AS       SELECT Value      Name      CONVERT XML   lt Names gt  lt name gt           REPLACE Name        lt  name gt  lt name gt        lt  name gt  lt  Names gt    AS xmlname       FROM tblnames     SELECT Value         xmlname value   Names 1  name 1    varchar 100    AS Name       xmlname value   Names 1  name 2    varchar 100    AS Surname  FROM Split Names   and also check the link below for reference  http   jahaines blogspot in 2009 06 converting-delimited-string-of-values html

User · Answer

I think PARSENAME is the neat function to use for this example  as described in this article  http   www sqlshack com parsing-and-rotating-delimited-data-in-sql-server-2012    The PARSENAME function is logically designed to parse four-part object names  The nice thing about PARSENAME is that it   s not limited to parsing just SQL Server four-part object names     it will parse any function or string data that is delimited by dots   The first parameter is the object to parse  and the second is the integer value of the object piece to return  The article is discussing parsing and rotating delimited data - company phone numbers  but it can be used to parse name surname data also    Example   USE COMPANY  SELECT PARSENAME  Whatever you want parsed  3  AS  ReturnValue     The article also describes using a Common Table Expression  CTE  called    replaceChars     to run PARSENAME against the delimiter-replaced values  A CTE is useful for returning a temporary view or result set    After that  the UNPIVOT function has been used to convert some columns into rows  SUBSTRING and CHARINDEX functions have been used for cleaning up the inconsistencies in the data  and the LAG function  new for SQL Server 2012  has been used in the end  as it allows referencing of previous records

User · Answer

You can use split function   SELECT   select top 1 item from dbo Split FullName      where id 1   as Name   select top 1 item from dbo Split FullName      where id 2   as Surname  FROM MyTbl

User · Answer

CREATE FUNCTION  dbo   fn split string to column         string NVARCHAR MAX        delimiter CHAR 1        RETURNS  out put TABLE        column id  INT IDENTITY 1  1  NOT NULL       value  NVARCHAR MAX        AS BEGIN     DECLARE  value NVARCHAR MAX            pos INT   0           len INT   0      SET  string   CASE              WHEN RIGHT  string  1      delimiter                 THEN  string    delimiter             ELSE  string             END      WHILE CHARINDEX  delimiter   string   pos   1   gt  0     BEGIN         SET  len   CHARINDEX  delimiter   string   pos   1  -  pos         SET  value   SUBSTRING  string   pos   len           INSERT INTO  out put   value           SELECT LTRIM RTRIM  value   AS  column           SET  pos   CHARINDEX  delimiter   string   pos    len    1     END      RETURN END

User · Answer

xml base answer is simple and clean  refer this   DECLARE  S varchar max            Split char 1            X xml  SELECT  S    ab cd ef gh ij           Split        SELECT  X   CONVERT xml    lt root gt   lt myvalue gt     REPLACE  S  Split   lt  myvalue gt   lt myvalue gt        lt  myvalue gt     lt  root gt      SELECT  T c value      varchar 20                  --retrieve ALL values at once   T c value    root myvalue  1    VARCHAR 20       --retrieve index 1 only  which is the  ab    T c value    root myvalue  2    VARCHAR 20     FROM  X nodes   root myvalue   T c

User · Answer

Using instring function       select Value          substring String 1 instr String      -1  Fname           substring String instr String       1  Sname  from tablename    Used two functions  1  substring string  position  length       returns string from positon to   length 2  instr string pattern      returns position of pattern   If we don   t provide length argument in substring it returns until end of string

User · Answer

You may find the solution in SQL User Defined Function to Parse a Delimited String helpful  from The Code Project    This is the code part from this page   CREATE FUNCTION  fn ParseText2Table      p SourceText VARCHAR MAX      p Delimeter VARCHAR 100      --default to comma delimited       RETURNS  retTable   TABLE  Position  INT IDENTITY 1 1       Int Value  INT      Num Value  NUMERIC 18 3       Txt Value  VARCHAR MAX       Date value  DATETIME      AS                                                                                     Purpose  Parse values from a delimited string    amp  return the result as an indexed table Copyright 1996  1997  2000  2003 Clayton Groom   lt A href  mailto Clayton Groom hotmail com  gt Clayton Groom hotmail com lt  A gt   Posted to the public domain Aug  2004 2003-06-17 Rewritten as SQL 2000 function   Reworked to allow for delimiters  gt  1 character in length  and to convert Text values to numbers 2016-04-05 Added logic for date values based on  new  ISDATE   function  Updated to use XML approach  which is more efficient                                                                                        BEGIN  DECLARE  w xml xml   SET  w xml   N  lt root gt  lt i gt     replace  p SourceText   p Delimeter   lt  i gt  lt i gt        lt  i gt  lt  root gt       INSERT INTO  retTable        Int Value         Num Value         Txt Value         Date value              SELECT CASE        WHEN ISNUMERIC  i  value       VARCHAR MAX       1        THEN CAST CAST  i  value       VARCHAR MAX    AS NUMERIC  AS INT        END AS  Int Value        CASE        WHEN ISNUMERIC  i  value       VARCHAR MAX       1        THEN CAST  i  value       VARCHAR MAX    AS NUMERIC 18  3         END AS  Num Value         i  value       VARCHAR MAX    AS  txt Value        CASE        WHEN ISDATE  i  value       VARCHAR MAX       1        THEN CAST  i  value       VARCHAR MAX    AS DATETIME        END AS  Num Value       FROM  w xml nodes    root i   AS  Items   i     RETURN  END  GO

User · Answer

With CROSS APPLY  select ParsedData    from MyTable mt cross apply   select str   mt String          f1 cross apply   select p1   charindex       str     ap1 cross apply   select p2   charindex       str  p1   1     ap2 cross apply   select Nmame   substring  str  1  p1-1                                         Surname   substring  str  p1 1  p2-p1-1               ParsedData

User · Answer

question is simple  but problem is hot     So I create some wrapper for string split   which pivot result in more generic way  It s table function which returns values  nn  value1  value2        value50  - enough for most CSV lines  If there are more values  they will wrap to next line - nn indicate line number  Set third parameter  columnCnt    yourNumber  to wrap at specific position   alter FUNCTION fn Split50           str varchar max        delim char 1         columnCnt int   50   RETURNS TABLE  AS RETURN        SELECT        FROM  SELECT              nn    nn - 1     columnCnt   1               nnn    value    cast   nn - 1     columnCnt    1 as varchar 10                 value         FROM  SELECT              nn   ROW NUMBER   over  order by  select null                value             FROM string split  str   delim  aa               aa         where nn  gt  0       bb     PIVOT               max value       FOR nnn IN               value1  value2  value3  value4  value5  value6  value7  value8  value9  value10          value11  value12  value13  value14  value15  value16  value17  value18  value19  value20          value21  value22  value23  value24  value25  value26  value27  value28  value29  value30          value31  value32  value33  value34  value35  value36  value37  value38  value39  value40          value41  value42  value43  value44  value45  value46  value47  value48  value49  value50                        AS PivotTable      Example of using   select   from dbo fn split50  zz1 aa2 ss3 dd4 ff5        DEFAULT      select   from dbo fn split50  zz1 aa2 ss3 dd4 ff5 gg6 hh7 jj8 ww9 qq10        3      select   from dbo fn split50  zz1 11 aa2 22 ss3 33 dd4 44 ff5 55 gg6 66 hh7 77 jj8 88 ww9 99 qq10 1010       2      Hope  it will helps

User · Answer

With SQL Server 2016 we can use string split to accomplish this   create table commasep    id int identity 1 1    string nvarchar 100     insert into commasep  string  values   John  Adam      test1 test2 test3    select id   value  as String from commasep   cross apply string split string

User · Answer

Try below   USE TRIAL GO CREATE TABLE DETAILS     ID INT    NAME VARCHAR 50     ADDRESS VARCHAR 50     INSERT INTO DETAILS VALUES  100   POPE-JOHN-PAUL   VATICAN CIT ROME ITALY     240   SIR-PAUL-McARTNEY   NEWYORK CITY NEWYORK USA     460  BARRACK-HUSSEIN-OBAMA   WHITE HOUSE WASHINGTON USA     700   PRESIDENT-VLADAMIR-PUTIN   RED SQUARE MOSCOW RUSSIA     950   NARENDRA-DAMODARDAS-MODI   10 JANPATH NEW DELHI INDIA     Query   select  ID    NAME    ADDRESS   REPLACE LEFT NAME  CHARINDEX  -   NAME    -       as First Name  CASE  WHEN CHARINDEX  -  REVERSE NAME    CHARINDEX  -  NAME   lt  LEN NAME  THEN  SUBSTRING NAME  CHARINDEX  -    NAME     1  LEN NAME  - CHARINDEX  -   REVERSE NAME   - CHARINDEX  -   NAME   ELSE  NULL  END AS Middle Name  REPLACE REVERSE  SUBSTRING  REVERSE NAME   1  CHARINDEX  -  REVERSE NAME       -      AS Last Name   REPLACE LEFT ADDRESS  CHARINDEX      ADDRESS            AS Locality  CASE  WHEN CHARINDEX     REVERSE ADDRESS    CHARINDEX     ADDRESS   lt  LEN ADDRESS   THEN SUBSTRING ADDRESS  CHARINDEX       ADDRESS   1  LEN ADDRESS -CHARINDEX      REVERSE ADDRESS  -CHARINDEX     ADDRESS   ELSE  Null   END AS STATE  REPLACE REVERSE SUBSTRING REVERSE ADDRESS  1  CHARINDEX     REVERSE ADDRESS             AS Country FROM DETAILS  SELECT CHARINDEX  -   REVERSE NAME   AS LAST CHARINDEX  -  NAME AS FIRST  LEN NAME  AS LENGTH FROM DETAILS  SELECT SUBSTRING NAME  CHARINDEX  -    NAME   1  LEN NAME  -CHARINDEX  -   REVERSE NAME   - CHARINDEX  -   NAME   FROM DETAILS   LET ME KNOW IF YOU HAVE ANY DOUBTS UNDERSTANDING THE CODE

User · Answer

This function is most fast   CREATE FUNCTION dbo F ExtractSubString      String VARCHAR MAX      NroSubString INT     Separator VARCHAR 5    RETURNS VARCHAR MAX  AS BEGIN     DECLARE  St INT   0   End INT   0   Ret VARCHAR MAX      SET  String    String    Separator     WHILE CHARINDEX  Separator   String   End   1   gt  0 AND  NroSubString  gt  0     BEGIN         SET  St    End   1         SET  End   CHARINDEX  Separator   String   End   1          SET  NroSubString    NroSubString - 1     END     IF  NroSubString  gt  0         SET  Ret          ELSE         SET  Ret   SUBSTRING  String   St   End -  St      RETURN  Ret END GO   Example usage   SELECT dbo F ExtractSubString COLUMN  1                dbo F ExtractSubString COLUMN  2                dbo F ExtractSubString COLUMN  3        FROM   TABLE

User · Answer

I re-wrote an answer above and made it better   CREATE FUNCTION  dbo   CSVParser       s        VARCHAR 255      idx      NUMERIC   RETURNS VARCHAR 12  BEGIN     DECLARE  comma int     SET  comma   CHARINDEX       s      WHILE 1 1     BEGIN         IF  comma 0             IF  idx 1                 RETURN  s             ELSE                 RETURN             IF  idx 1         BEGIN             DECLARE  word VARCHAR 12              SET  word LEFT  s   comma - 1              RETURN  word         END          SET  s   RIGHT  s LEN  s - comma          SET  comma   CHARINDEX       s          SET  idx    idx - 1     END     RETURN  not used  END   Example usage   SELECT dbo CSVParser COLUMN  1          dbo CSVParser COLUMN  2          dbo CSVParser COLUMN  3  FROM   TABLE

User · Answer

Use Parsename   function  with cte as      select  Aria Karimi  as FullName     Union     select  Joe Karimi  as FullName     Union     select  Bab Karimi  as FullName    SELECT PARSENAME REPLACE FullName          2  as Name          PARSENAME REPLACE FullName          1  as Family     FROM cte   Result   Name    Family -----   ------ Aria    Karimi Bab     Karimi Joe     Karimi

User · Answer

We can create a function as this  CREATE Function  dbo   fn CSVToTable          CSVList Varchar max    RETURNS  Table TABLE  ColumnData VARCHAR 100   AS BEGIN     IF RIGHT  CSVList  1   lt  gt          SELECT  CSVList    CSVList            DECLARE  Pos    BIGINT               OldPos BIGINT     SELECT   Pos      1               OldPos   1      WHILE    Pos  lt  LEN  CSVList          BEGIN             SELECT   Pos   CHARINDEX       CSVList   OldPos              INSERT INTO  Table             SELECT  LTRIM RTRIM SUBSTRING  CSVList   OldPos   Pos -  OldPos    Col001              SELECT   OldPos    Pos   1         END      RETURN END   We can then seperate the CSV values into our respective columns using a SELECT statement

User · Answer

SELECT id         Substring NAME  0  Charindex      NAME               AS firstname         Substring NAME  Charindex      NAME   Len NAME    1  AS lastname FROM   spilt

User · Answer

mytable   Value  ColOne -------------------- 1      Cleo  Smith   The following should work if there aren t too many columns  ALTER TABLE mytable ADD ColTwo nvarchar 256   UPDATE mytable SET ColTwo   LEFT ColOne  Charindex      ColOne  - 1   -- Cleo    LEFT  Cleo  Smith   Charindex       Cleo  Smith   - 1  UPDATE mytable SET ColTwo   REPLACE ColOne  ColTwo             --  Smith    REPLACE  Cleo  Smith    Cleo         UPDATE mytable SET ColOne   REPLACE ColOne        ColTwo       ColTwo   LTRIM ColTwo   -- Cleo    REPLACE  Cleo  Smith           Smith          Result   Value  ColOne ColTwo -------------------- 1      Cleo   Smith

User · Answer

I found that using PARSENAME as above caused any name with a period to get nulled   So if there was an initial or a title in the name followed by a dot they return NULL     I found this worked for me    SELECT  REPLACE SUBSTRING FullName  1 CHARINDEX      FullName            as Name  REPLACE SUBSTRING FullName  CHARINDEX      FullName   LEN FullName             as Surname FROM Table1

User · Answer

select distinct modelFileId F4   from contract cross apply  select XmlList convert xml    lt x gt   replace modelFileId       lt  x gt  lt x gt      lt  x gt    query       F2 cross apply  select mfid1 XmlNode value   x 1    varchar 512     mfid2 XmlNode value   x 2    varchar 512     mfid3 XmlNode value   x 3    varchar 512     mfid4 XmlNode value   x 4    varchar 512    from XmlList nodes  x   F3 XmlNode   F4 where modelFileId like       order by modelFileId

User · Answer

You can use SQL Server s  STRING SPLIT  functions   STRING SPLIT   string   separator

User · Answer

Try this  change instances of     to     or whatever delimiter you want to use   CREATE FUNCTION dbo Wordparser      multiwordstring VARCHAR 255      wordnumber      NUMERIC   returns VARCHAR 255  AS   BEGIN       DECLARE  remainingstring VARCHAR 255        SET  remainingstring  multiwordstring        DECLARE  numberofwords NUMERIC       SET  numberofwords  LEN  remainingstring  - LEN REPLACE  remainingstring              1         DECLARE  word VARCHAR 50        DECLARE  parsedwords TABLE                  line NUMERIC IDENTITY 1  1            word VARCHAR 255                 WHILE  numberofwords  gt  1         BEGIN             SET  word LEFT  remainingstring  CHARINDEX       remainingstring  - 1               INSERT INTO  parsedwords word              SELECT  word              SET  remainingstring  REPLACE  remainingstring  Concat  word                        SET  numberofwords  LEN  remainingstring  - LEN REPLACE  remainingstring              1               IF  numberofwords   1               BREAK              ELSE               CONTINUE         END        IF  numberofwords   1         SELECT  word    remainingstring       INSERT INTO  parsedwords word        SELECT  word        RETURN          SELECT word          FROM    parsedwords          WHERE  line    wordnumber     END   Example usage   SELECT dbo Wordparser COLUMN  1          dbo Wordparser COLUMN  2          dbo Wordparser COLUMN  3  FROM   TABLE

User · Answer

it is so easy  you can take it by below query   DECLARE  str NVARCHAR MAX   ControlID 05436b78-04ba-9667-fa01-9ff8c1b7c235 3  SELECT LEFT  str  CHARINDEX      str -1  RIGHT  str LEN  str - CHARINDEX      str

User · Answer

I think following function will work for you   You have to create a function in SQL first  Like this  CREATE FUNCTION  dbo   fn split    str VARCHAR MAX    delimiter CHAR 1    RETURNS  returnTable TABLE  idx INT PRIMARY KEY IDENTITY  item VARCHAR 8000   AS BEGIN DECLARE  pos INT SELECT  str    str    delimiter WHILE LEN  str   gt  0      BEGIN         SELECT  pos   CHARINDEX  delimiter  str          IF  pos   1             INSERT  returnTable  item                  VALUES  NULL          ELSE             INSERT  returnTable  item                  VALUES  SUBSTRING  str  1   pos-1           SELECT  str   SUBSTRING  str   pos 1  LEN  str - pos             END RETURN END   You can call this function  like this   select   from fn split  1 24 5         Implementation   Declare  test TABLE   ID VARCHAR 200   Data VARCHAR 200     insert into  test   ID  Data  Values   1   Cleo Smith     insert into  test   ID  Data  Values   2   Paul Grim    select ID   select item from fn split Data      where idx in  1   as Name    select item from fn split Data      where idx in  2   as Surname  from  test   Result will like this

User · Answer

ALTER function get occurance index  delimiter varchar 1   occurence int  String varchar 100   returns int AS Begin --Declare  delimiter varchar 1       occurence int 2  String varchar 100   a b c  Declare  result int   with T as       select 1 Rno 0 as row  charindex  delimiter   String  pos  String st     union all     select Rno 1 pos   1  charindex  delimiter   String  pos   1    String     from T     where pos  gt  0   select   result pos  from T  where pos  gt  0   and rno    occurence  return isnull  result 0  ENd   declare  data as table  data varchar 100   insert into  data values  1 2 3    insert into  data values  aaa bbbbb cccc    select top  3 Substring  data 0 dbo get occurance index      1 data    --First Record always starts with 0 Substring  data dbo get occurance index      1 data  1 dbo get occurance index      2 data -dbo get occurance index      1 data -1    Substring  data dbo get occurance index      2 data  1 len data     -- Last record cant be more than len of actual data data  From  data

[sql-server] How to split a comma-separated value to columns

Examples related to sql-server

Examples related to sql-server-2008

Examples related to csv