Splitting up of values in single column

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

Splitting up of values in single column

Author

Topic

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2008-05-21 : 17:01:28

Hi,

I have a table that has multiple postal codes in one of the columns. Those have to be split up one per line and stored in another table. The zip codes are comma seperated. Is there a function that can do this...?

Example data in ZipCodeTable. (Name and ZipCode are 2 columns in a table)

Name ZipCode
Area1 19930,19970,19971,19944
Area2 19934,19938,19901,19903,19904
Area3 19994,19838

output Table should be:

Name ZipCode
Area1 19930
Area1 19970
Area1 19971
Area1 19944
Area2 19934
Area2 19938
Area2 19901
Area2 19903
Area2 19904
Area3 19994
Area3 19838

Any thoughts on this would be of much help !!..

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 17:17:11

Using Microsoft SQL Server 2000 or Microsoft SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2008-05-21 : 17:22:08

using SQL Server 2005..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 17:28:57

[code]DECLARE @Sample TABLE (Name VARCHAR(9), ZipCodes VARCHAR(99))

INSERT @Sample
SELECT 'Area1', '19930,19970,19971,19944' UNION ALL
SELECT 'Area2', '19934,19938,19901,19903,19904' UNION ALL
SELECT 'Area3', '19994,19838'

-- SQL Server 2005 ordered by listentry
SELECT s.Name,
p.Data AS ZipCode
FROM @Sample AS s
CROSS APPLY dbo.fnParseList(',', s.ZipCodes) AS p
ORDER BY s.Name,
p.RowID

-- SQL Server 2005 ordered by ZipCode
SELECT s.Name,
p.Data AS ZipCode
FROM @Sample AS s
CROSS APPLY dbo.fnParseList(',', s.ZipCodes) AS p
ORDER BY s.Name,
p.Data

-- SQL Server 2000 ordered by listentry
SELECT s.Name,
SUBSTRING(s.ZipCodes, v.Number - 1, COALESCE(NULLIF(CHARINDEX(',', s.ZipCodes, v.Number), 0), LEN(s.ZipCodes) + 1) - v.Number + 1) AS ZipCode
FROM @Sample AS s
INNER JOIN master..spt_values AS v ON v.Type = 'p'
WHERE SUBSTRING(',_' + s.ZipCodes, v.Number, 1) = ','
ORDER BY s.Name,
v.Number

-- SQL Server 2000 ordered by ZipCode
SELECT s.Name,
SUBSTRING(s.ZipCodes, v.Number - 1, COALESCE(NULLIF(CHARINDEX(',', s.ZipCodes, v.Number), 0), LEN(s.ZipCodes) + 1) - v.Number + 1) AS ZipCode
FROM @Sample AS s
INNER JOIN master..spt_values AS v ON v.Type = 'p'
WHERE SUBSTRING(',_' + s.ZipCodes, v.Number, 1) = ','
ORDER BY s.Name,
2[/code]

E 12°55'05.25"
N 56°04'39.16"

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2008-05-21 : 17:39:55

Thank you so much... I tested the queries for sql server 2000 the output is perfect. In the sql server 2005 example i am getting an error -- Invalid object name 'dbo.fnParseList'. Where is the function available.. ?
I guess i am missing something..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 01:40:47

quote:
Originally posted by pvccaz

Thank you so much... I tested the queries for sql server 2000 the output is perfect. In the sql server 2005 example i am getting an error -- Invalid object name 'dbo.fnParseList'. Where is the function available.. ?
I guess i am missing something..

Its a function available inside this forums. Look inside this forums for function code.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-22 : 05:48:08

quote:
Originally posted by pvccaz

Thank you so much... I tested the queries for sql server 2000 the output is perfect. In the sql server 2005 example i am getting an error -- Invalid object name 'dbo.fnParseList'. Where is the function available.. ?
I guess i am missing something..

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

Madhivanan

Failing to plan is Planning to fail

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2008-05-22 : 13:05:18

Thanks you visakh16 and madhivanan !!..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources