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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel ReynoldsSoftware 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
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 KumarDatabase 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.