Split one comma delimited string in a column into 100

I have a table called tmp_tbl that only has one column called field1.
I populate that column with a single comma delimited string that has approx 100 commas or splits
How can I get this one column to create 100 columns with each piece of delimited data?

I have looked at every possible split function and can't find one that I can get to work.
Thanks in advance!
Who is Participating?
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.

Kent OlsenData Warehouse Architect / DBACommented:
Hi H-SC,

How many rows are in the table?
Is this a process that you'll want to run more than once?
Deepak SubburajanDB DeveloperCommented:
You can achieve this by Dynamic Queries.

Lets assume your table is "mytable" and the one column is "commacolumns"

Declare @buildtable VARCHAR(255)

SELECT @buildtable = 'SELECT ' + REPLACE(commacolumns, ',' , 'VARCHAR(30),') from mytable

SELECT @buildtable = @buildtable + ' INTO newtablename'

EXEC @buildtable

Open in new window

Vikas GargBusiness Intelligence DeveloperCommented:
CREATE FUNCTION [dbo].[CommaSeparatedToTable] ( @StringInput VARCHAR(8000), @Delimiter nvarchar(1))
RETURNS @OutputTable TABLE ( [String] VARCHAR(50) )

    DECLARE @String    VARCHAR(50)

    WHILE LEN(@StringInput) > 0
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput) - 1, -1),
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( [String] )
        VALUES ( @String )


Open in new window


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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

H-SCAuthor Commented:
Thanks for the reply....
There will be only several rows at any given time in the table.
Yes, this will be ran on a daily basis.
H-SCAuthor Commented:
Deepak Subburajan,
I ran your solution and got an error of
"the name 'SELECT .......and then my string of data .... is not a valid identifier"

any ideas?
H-SCAuthor Commented:
Vikas Garg,
Can you give an example of the use of the function?
Deepak SubburajanDB DeveloperCommented:

Please read through your question once again. You mentioned "create 100 columns" which means you wanted to split the single column into multiple columns(same/new table). I think you were about to mean "split the single cell into multiple rows". Isn't it?
Kent OlsenData Warehouse Architect / DBACommented:
Daily basis.  Rats.  So much for just copying the data into Excel and letting its tools split the data

Meta-SQL is probably the way to go.  There is already a post steering you that way.

If all of the data is numeric,

  SELECT 'INSERT INTO mytable SELECT ' + columnvalue FROM sometable;

Will generate the SQL to insert the data.  However, it's more complicated if the columns are character types, or if every row doesn't contain the exact number of columns that are defined in the table.
H-SCAuthor Commented:
Deepak Subburajan,
Well I need to do the following

field1 in my table looks like this:
10022, test, test122, helloworld, , , test5, helloworld2,

I need field1 to break apart into multiple columns not as rows...
so field1 would now look like:

col1      col2    col3        col4              col5
10022   test    test122   helloworld           .......... and so on.
Kent OlsenData Warehouse Architect / DBACommented:
SQL Server will allow you to quote every field.  It's not terribly efficient, but for a few rows it won't matter.

with D
(  SELECT 'a,1.2,asdf' Txt
select 'INSERT INTO T SELECT ''' + REPLACE (txt, ',', ''', ''') + '''' FROM D;

As long as the data in the string is in column order, all is good.

Good Luck!
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
Microsoft SQL Server

From novice to tech pro — start learning today.

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.