Split Column - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Split Column

Author

Topic

gavinjb
Starting Member

34 Posts

Posted - 2010-09-15 : 06:58:11

Hi,

I am trying to write a function to split a column of data into multiple columns, the only problem is the sperator is not the usual single character which makes it difficult to use charindex to split and the only other way I can think of currently solving the problem is to loop through every character in the string, but this will not be that afficient.

The Data looks as follows
value1[:CSV:]value2[:CSV:]value3

as the amount of columns you would get out is not determined I would want the output as a table so I am trying to write a tbf (table valued function)

Can anyone help?

Thanks,

Gavin,

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-09-15 : 07:25:41

If you use REPLACE(column,'[:CSV:]',',') instead of just column, you could still use charindex..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-09-15 : 07:34:05

use one of these
fnParseString
fnParseList

KH
[spoiler]Time is always against us[/spoiler]

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-09-15 : 11:30:55

Here's function that can be used for all parsing provided by jeff Moden.

Create function [dbo].[strSplit] (@arr AS NVARCHAR(MAX), @sep AS NVARCHAR(10))
RETURNS TABLE
AS
RETURN
WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1) --2 rows
,L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B) --4 rows (2x2)
,L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B) --16 rows (4x4)
,L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B) --256 rows (16x16)
,L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B) --65536 rows (256x256)
,L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B) --4,294,967,296 rows (65536x65536)
,Numbers AS (
SELECT row_number() OVER (ORDER BY (SELECT 0)) AS N FROM L5)

SELECT (n - 1) - LEN(REPLACE(LEFT(@arr, n-1), @sep, N'')) + 1 AS pos,
LTRIM(SUBSTRING(@arr, n, CHARINDEX(@sep, @arr + @sep, n) - n)) AS element
FROM Numbers
WHERE n <= LEN(@arr) + 1
AND SUBSTRING(@sep + @arr, n, len(@sep)) = @sep
AND Numbers.n <= 1000

Then call it

declare @string varchar(max)

set @string = 'value1[:CSV:]value2[:CSV:]value3'

select * from dbo.strSplit(@string, '[:CSV:]')

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources