Split Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Split Function

Author

Topic

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-11-06 : 08:05:52

Does anyone know of a function I can use that will split 1 column into multiple columns based on a delimiter (,)?

I also want to be able to name the column.

Data is currently in format 1,2,3,4,5,6 abd I want it to be

ColA ColB ColC ColD ColE ColF
1 2 3 4 5 6

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-06 : 08:22:07

Are there a fixed number of columns?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-11-06 : 08:40:50

There will be a fixed number of 100 columns, but not all columns will necessarily have entries

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-11-06 : 11:11:24

I have found the following code that may work, but it is in MYSQL, does anyone know of a way I can convert the syntax to MSSQL?

CREATE FUNCTION SPLIT_STR(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, '');

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-07 : 06:08:51

[code]CREATE TABLE #test(id int, data varchar(100))

INSERT INTO #test VALUES (1,'This,is,a,test,string')
INSERT INTO #test VALUES (2,'See,if,it,can,be,split,into,many,columns')

SELECT * FROM #test
DECLARE @pivot varchar(8000)
DECLARE @select varchar(8000)

SELECT
@pivot=coalesce(@pivot+',','')+'[col'+cast(number+1 as varchar(10))+']'
FROM
master..spt_values where type='p' and
number<=(SELECT max(len(data)-len(replace(data,',',''))) FROM #test)

SELECT
@select='
select p.*
from (
select
id,substring(data, start+2, endPos-Start-2) as token,
''col''+cast(row_number() over(partition by id order by start) as varchar(10)) as n
from (
select
id, data, n as start, charindex('','',data,n+2) endPos
from (select number as n from master..spt_values where type=''p'') num
cross join
(
select
id, '','' + data +'','' as data
from
#test
) m
where n < len(data)-1
and substring(data,n+1,1) = '','') as data
) pvt
Pivot ( max(token)for n in ('+@pivot+'))p'

EXEC(@select)

DROP TABLE #test
[/code]

--
Chandu

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-11-07 : 06:22:50

quote:
Originally posted by bandi

CREATE TABLE #test(id int, data varchar(100))

INSERT INTO #test VALUES (1,'This,is,a,test,string')
INSERT INTO #test VALUES (2,'See,if,it,can,be,split,into,many,columns')

SELECT * FROM #test
DECLARE @pivot varchar(8000)
DECLARE @select varchar(8000)

SELECT
@pivot=coalesce(@pivot+',','')+'[col'+cast(number+1 as varchar(10))+']'
FROM
master..spt_values where type='p' and
number<=(SELECT max(len(data)-len(replace(data,',',''))) FROM #test)

SELECT
@select='
select p.*
from (
select
id,substring(data, start+2, endPos-Start-2) as token,
''col''+cast(row_number() over(partition by id order by start) as varchar(10)) as n
from (
select
id, data, n as start, charindex('','',data,n+2) endPos
from (select number as n from master..spt_values where type=''p'') num
cross join
(
select
id, '','' + data +'','' as data
from
#test
) m
where n < len(data)-1
and substring(data,n+1,1) = '','') as data
) pvt
Pivot ( max(token)for n in ('+@pivot+'))p'

EXEC(@select)

DROP TABLE #test

--
Chandu

Sorry to be a pain, but how can I use that code against an existing view?

It is named HostApplication and has two columns Host and Application, with application being the string of application seperated by a comma.

So in your example id would need to be the host.

I also need to use this in a report, how would I do that.

Sorry - I am very much an SQL novice with basic experience of creating views and usimg in reports when some experience of stored procedures purely to set report parameters.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-07 : 07:01:27

Borrowing Chandu's sample data, this is another approach. To use this, you will need to first install the string split function from this article. The function is in Fig. 21. Copy and paste that to an SSMS window and run it to install the function. Then, the code will be like this:

CREATE TABLE #test(id int, data varchar(100))

INSERT INTO #test VALUES (1,'This,is,a,test,string')
INSERT INTO #test VALUES (2,'See,if,it,can,be,split,into,many,columns') 

SELECT * FROM #test

SELECT * FROM
(
	SELECT
		Id, ItemNumber, Item
	FROM
		#test
		CROSS APPLY  dbo.DelimitedSplit8K([data],',') dsk
)s PIVOT
(MAX (Item) FOR ItemNumber IN  
	([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
	[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
	[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
	[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],
	[41],[42],[43],[44],[45],[46],[47],[48],[49],[50]
))P

DROP TABLE #test;

To use it with your view, change it as shown below - it is the same as the code above, except, I have replaced the test table name and column names with your table name and column names:

SELECT * FROM
(
	SELECT
		Host, ItemNumber, Item
	FROM
		HostApplication
		CROSS APPLY  dbo.DelimitedSplit8K([Application],',') dsk
)s PIVOT
(MAX (Item) FOR ItemNumber IN  
	([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
	[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
	[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
	[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],
	[41],[42],[43],[44],[45],[46],[47],[48],[49],[50]
))P

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-11-07 : 07:29:50

quote:
Originally posted by sunitabeck

Borrowing Chandu's sample data, this is another approach. To use this, you will need to first install the string split function from this article. The function is in Fig. 21. Copy and paste that to an SSMS window and run it to install the function. Then, the code will be like this:
CREATE TABLE #test(id int, data varchar(100))

INSERT INTO #test VALUES (1,'This,is,a,test,string')
INSERT INTO #test VALUES (2,'See,if,it,can,be,split,into,many,columns')

SELECT * FROM #test

SELECT * FROM
(
SELECT
Id, ItemNumber, Item
FROM
#test
CROSS APPLY dbo.DelimitedSplit8K([data],',') dsk
)s PIVOT
(MAX (Item) FOR ItemNumber IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],
[41],[42],[43],[44],[45],[46],[47],[48],[49],[50]
))P

DROP TABLE #test;

To use it with your view, change it as shown below - it is the same as the code above, except, I have replaced the test table name and column names with your table name and column names:
SELECT * FROM
(
SELECT
Host, ItemNumber, Item
FROM
HostApplication
CROSS APPLY dbo.DelimitedSplit8K([Application],',') dsk
)s PIVOT
(MAX (Item) FOR ItemNumber IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],
[41],[42],[43],[44],[45],[46],[47],[48],[49],[50]
))P