• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5239
  • Last Modified:

Split one comma delimited string in a column into 100

Hello,
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!
0
H-SC
Asked:
H-SC
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
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?
0
 
Deepak SubburajanCommented:
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

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

    DECLARE @String    VARCHAR(50)

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

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

    RETURN
END

Open in new window

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
H-SCAuthor Commented:
kdo,
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.
0
 
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?
0
 
H-SCAuthor Commented:
Vikas Garg,
Can you give an example of the use of the function?
0
 
Deepak SubburajanCommented:
H-SC,

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?
0
 
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.
0
 
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.
0
 
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
as
(  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!
Kent
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now