January 12, 2007 at 1:10 pm
Hi I need to split data in a field where ; is the delimiter.
i.e.
Name
Joe; Bloggs
David; Peter; Paul
Result should be:
Name Name1 Name2 Name3
Joe; Bloggs Joe Bloggs
David; peter; Paul David Peter Paul
I am playing and using the charindex function but no luck so far.
Thanks in advance!!
p.s I need to get this done by Sat evening!
January 12, 2007 at 9:24 pm
Here is the function I use for this purpose: It requires a numbers table. It will split a string based on any single or multi byte delimiter.
CREATE FUNCTION dbo.ParseString3 (@String VARCHAR(8000), @Delimiter VARCHAR(10))
RETURNS TABLE
AS
/*******************************************************************************************************
* ParseString
*
* Creator: Robert Cary
* Date: 9/12/2006
*
*
* Outline: Takes a string that is delimited by another string (of one or more characters),
* parses it out into tokens and returns the tokens in table format. Leading
* and trailing spaces in each token are removed, and empty tokens are thrown
* away.
*
*
* Usage examples:
Single-byte delimiter:
select * from dbo.ParseString3('|HDI|TR|YUM|||', '|')
select * from dbo.ParseString3('HDI| || TR |YUM', '|')
select * from dbo.ParseString3(' HDI| || S P A C E S |YUM | ', '|')
select * from dbo.ParseString3('HDI|||TR|YUM', '|')
select * from dbo.ParseString3('', '|')
select * from dbo.ParseString3('YUM', '|')
select * from dbo.ParseString3('||||', '|')
select * from dbo.ParseString3('HDI TR YUM', ' ')
select * from dbo.ParseString3(' HDI| || S P A C E S |YUM | ', ' ') order by Ident
select * from dbo.ParseString3(' HDI| || S P A C E S |YUM | ', ' ') order by StringValue
Multi-byte delimiter:
select * from dbo.ParseString3('HDI and TR', 'and')
select * from dbo.ParseString3('Pebbles and Bamm Bamm', 'and')
select * from dbo.ParseString3('Pebbles and sandbars', 'and')
select * from dbo.ParseString3('Pebbles and sandbars', ' and ')
select * from dbo.ParseString3('Pebbles and sand', 'and')
select * from dbo.ParseString3('Pebbles and sand', ' and ')
*
*
* Notes:
*
*
* Modifications
*
*
********************************************************************************************************/
RETURN (
SELECT Ident, StringValue FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY Num) AS Ident,
CASE
WHEN DATALENGTH(@delimiter) = 0 OR @delimiter IS NULL
THEN SUBSTRING(@string, num, 1)
ELSE
LTRIM(RTRIM(SUBSTRING(@String,
CASE
WHEN (Num = 1 AND SUBSTRING(@String,num ,DATALENGTH(@delimiter)) <> @delimiter) THEN 1
ELSE Num + DATALENGTH(@delimiter)
END,
CASE CHARINDEX(@Delimiter, @String, Num + DATALENGTH(@delimiter))
WHEN 0 THEN LEN(@String) - Num + DATALENGTH(@delimiter)
ELSE CHARINDEX(@Delimiter, @String, Num + DATALENGTH(@delimiter)) - Num -
CASE
WHEN Num > 1 OR (Num = 1 AND SUBSTRING(@String,num ,DATALENGTH(@delimiter)) = @delimiter)
THEN DATALENGTH(@delimiter)
ELSE 0
END
END
)))
END AS StringValue
FROM dbo.Numbers
WHERE Num <= LEN(@String)
AND (
SUBSTRING(@String, Num, DATALENGTH(ISNULL(@delimiter,''))) = @Delimiter
OR Num = 1
OR DATALENGTH(ISNULL(@delimiter,'')) = 0
)
) R WHERE DATALENGTH(StringValue) <> 0
)
SQL guy and Houston Magician
January 12, 2007 at 9:26 pm
Here is some code to build a numbers table.
CREATE TABLE dbo.Numbers
(
Num INT PRIMARY KEY CLUSTERED
)
INSERT INTO dbo.Numbers(Num)
SELECT (i5.[Num] * 1000 + i4.Num*1000 + i3.Num*100 + i2.Num*10 + i1.Num) AS num
FROM (SELECT 0 AS [Num] UNION ALL
SELECT 1 AS [Num] UNION ALL
SELECT 2 AS [Num] UNION ALL
SELECT 3 AS [Num] UNION ALL
SELECT 4 AS [Num] UNION ALL
SELECT 5 AS [Num] UNION ALL
SELECT 6 AS [Num] UNION ALL
SELECT 7 AS [Num] UNION ALL
SELECT 8 AS [Num] UNION ALL
SELECT 9 AS [Num]) AS i1
CROSS JOIN
(SELECT 0 AS [Num] UNION ALL
SELECT 1 AS [Num] UNION ALL
SELECT 2 AS [Num] UNION ALL
SELECT 3 AS [Num] UNION ALL
SELECT 4 AS [Num] UNION ALL
SELECT 5 AS [Num] UNION ALL
SELECT 6 AS [Num] UNION ALL
SELECT 7 AS [Num] UNION ALL
SELECT 8 AS [Num] UNION ALL
SELECT 9 AS [Num]) AS i2
CROSS JOIN
(SELECT 0 AS [Num] UNION ALL
SELECT 1 AS [Num] UNION ALL
SELECT 2 AS [Num] UNION ALL
SELECT 3 AS [Num] UNION ALL
SELECT 4 AS [Num] UNION ALL
SELECT 5 AS [Num] UNION ALL
SELECT 6 AS [Num] UNION ALL
SELECT 7 AS [Num] UNION ALL
SELECT 8 AS [Num] UNION ALL
SELECT 9 AS [Num]) AS i3
CROSS JOIN
(SELECT 0 AS [Num] UNION ALL
SELECT 1 AS [Num] UNION ALL
SELECT 2 AS [Num] UNION ALL
SELECT 3 AS [Num] UNION ALL
SELECT 4 AS [Num] UNION ALL
SELECT 5 AS [Num] UNION ALL
SELECT 6 AS [Num] UNION ALL
SELECT 7 AS [Num] UNION ALL
SELECT 8 AS [Num] UNION ALL
SELECT 9 AS [Num]) AS i4
CROSS JOIN
(SELECT 0 AS [Num] UNION ALL
SELECT 1 AS [Num] UNION ALL
SELECT 2 AS [Num] UNION ALL
SELECT 3 AS [Num] UNION ALL
SELECT 4 AS [Num] UNION ALL
SELECT 5 AS [Num] UNION ALL
SELECT 6 AS [Num] UNION ALL
SELECT 7 AS [Num] UNION ALL
SELECT 8 AS [Num] UNION ALL
SELECT 9 AS [Num]) AS i5
WHERE (i5.[Num] * 10000 + i4.Num*1000 + i3.Num*100 + i2.Num*10 + i1.Num) <> 0
ORDER BY [Num]
SQL guy and Houston Magician
January 12, 2007 at 9:31 pm
This code isn't quite what you're after, but it'll get you closer. I know you're on a timecrunch, it's late here and I don't have time to put together anything better. In case nobody else suggests anything in time, and you don't figure out something better, here is one approach.
It is an ugly hack (This function is NOT designed to be used like this and will perform very poorly as it has to hit the numbers table several times for every row in your results)
but here goes:
CREATE TABLE #test
(
Names VARCHAR(100)
)
INSERT INTO #TEST(Names)
VALUES('Peter; Paul; Mary')
INSERT INTO #TEST(Names)
VALUES('Joe; Bloggs; Company')
INSERT INTO #TEST(Names)
VALUES('Really; Really; Really; long; field')
SELECT Names, (SELECT StringValue FROM dbo.parsestring3(t.Names, ';') WHERE Ident = 1) AS Name1,
(SELECT StringValue FROM dbo.parsestring3(t.Names, ';') WHERE Ident = 2) AS Name2,
(SELECT StringValue FROM dbo.parsestring3(t.Names, ';') WHERE Ident = 3) AS Name3,
(SELECT StringValue FROM dbo.parsestring3(t.Names, ';') WHERE Ident = 4) AS Name4,
(SELECT StringValue FROM dbo.parsestring3(t.Names, ';') WHERE Ident = 5) AS Name5,
(SELECT StringValue FROM dbo.parsestring3(t.Names, ';') WHERE Ident = 6) AS Name6,
(SELECT StringValue FROM dbo.parsestring3(t.Names, ';') WHERE Ident = 7) AS Name7,
(SELECT StringValue FROM dbo.parsestring3(t.Names, ';') WHERE Ident = 8) AS Name8,
(SELECT StringValue FROM dbo.parsestring3(t.Names, ';') WHERE Ident = 9) AS Name9,
(SELECT StringValue FROM dbo.parsestring3(t.Names, ';') WHERE Ident = 10) AS Name10
FROM #test t
SQL guy and Houston Magician
January 13, 2007 at 12:17 pm
Thanks for all you suggestions guys!! I've been playing around and nearly there, should be fine for tonight.
Cheers!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply