Insert records with commas into a new table without commas SQL task

Hi all.

I have a SQL Server 2008 table (myTableA) with a field (Notes) that has data separated with commas:

Sample data: UPS, FedEx,DHL

I want to insert the data into a column in another table myTableB with each one being it's own row:

UPS
FedEx
DHL

How can I do this?

Thank you in advance!
printmediaAsked:
Who is Participating?
 
Daniel ReynoldsConnect With a Mentor Software Applications Developer / IntegratorCommented:
If you were on SQL Server 2016, you could use STRING_SPLIT to help you out.

Since you are on SQL2008, you will probably need to use a home brewed string split function.

The following splitstring function works to bring each item back in its own row

CREATE FUNCTION [dbo].[splitstring] ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

Open in new window


simply call it as follows:

SELECT * FROM dbo.splitstring('Monday,Tuesday,Wednesday,Thursday, Friday, Saturday,Sunday')

Open in new window


This might take a little playing around with on your part.
You may need to pull each field into a variable to process the function on it. which may not be very helpful depending on your scenario.
0
 
PortletPaulConnect With a Mentor freelancerCommented:
There are many string splitting functions available, this one is efficient  for varchar up to varchar(8000)

    CREATE FUNCTION [dbo].[DelimitedSplit8K]
    --===== Define I/O parameters
            (@pString VARCHAR(8000), @pDelimiter CHAR(1))
    --WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
    RETURNS TABLE WITH SCHEMABINDING AS
     RETURN
    --===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
         -- enough to cover VARCHAR(8000)
      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
    ;

Open in new window

function from : http://www.sqlservercentral.com/articles/Tally+Table/72993/


 
    CREATE TABLE myTableA
        (id int identity (1,1) primary key, [Notes] varchar(100))
    ;
        
    INSERT INTO myTableA
        ([Notes])
    VALUES
        ('UPS, FedEx,DHL')
    ;

   select
         id as MyTableAID
       , ItemNumber
       , Item
    from myTableA t
    cross apply dbo.DelimitedSplit8k(t.Notes,',') split

Open in new window

    | MyTableAID | ItemNumber |   Item |
    |------------|------------|--------|
    |          1 |          1 |    UPS |
    |          1 |          2 |  FedEx |
    |          1 |          3 |    DHL |

Open in new window


see it working here: http://sqlfiddle.com/#!6/da584b/1
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Well there are many ways to split a string.  If you want more details then please follow - https://sqlperformance.com/2016/03/sql-server-2016/string-split ( For performance the sequence of performance will be STRING_SPLIT ->  CLR function ->  XML -> JSON and then Numbers one ) So the above one will perform the worst out of the other methods i have given. Read more from the post for details.

You just need 4 lines. No need to use a function.

Below query is what you need.

DECLARE @x AS XML=''
DECLARE @Param AS VARCHAR(100) = 'UPS,FedEx,DHL'
SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)
INSERT INTO myTableA(Notes) 
SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t)

Open in new window


Sample trial SELECT Query

/*------------------------

 
DECLARE @x AS XML=''
DECLARE @Param AS VARCHAR(100) = 'UPS,FedEx,DHL'
SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)
SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t)

------------------------*/
Value
----------
UPS
FedEx
DHL

(3 row(s) affected)

Open in new window



Please follow my url for more methods to split a string-
https://msbiskills.com/2015/04/11/t-sql-query-multiple-ways-to-split-a-string-puzzle/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.