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

Author

Topic

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2012-02-13 : 11:57:39

I have a table that has 4 columns (ID, CustName, Country, Cities).The Cities column has multiple cities in the form of space delimited structure like - CityName State Zip CityName State Zip. These needs to be seperated out into 6 columns ID, CustName, Country, City, State, ZIP.

So the multiple cities(CityName State Zip CityName State Zip) which are in 1 column needs to be split into multiple columns with unique cityname, state and zip.

Example the input table is like the following:

ID CustName Country Cities
1 John USA Phoenix AZ 85569 Mesa AZ 89764 SanFransisco CA 78963
2 Peter USA SanAntonio TX 45697 Houston TX 56325

The output needs to be split as:
ID CustName Country City State ZIP
1 John USA Phoenix AZ 85569
1 John USA Mesa AZ 89764
1 John USA SanFransisco CA 78963
2 Peter USA SanAntonio TX 45697
2 Peter USA Houston TX 56325

I know that it is not the best way to store multiple cities in one column and it should have been a stored in a seperate tables, but this one of that old systems that i am working with.

I had see some sample scripts that splits space delimited characters, but is there a way to seperate delimited values after every 3 delimiters like the above example into individual columns ?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 12:06:18

yep its possible

see this link

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

create the above function and use like


SELECT ID,CustName,Country,Val,
MAX(CASE WHEN Rn=1 THEN Val END) AS City,
MAX(CASE WHEN Rn=2 THEN Val END) AS State,
MAX(CASE WHEN Rn=3 THEN Val END) AS ZIP
FROM
(
SELECT t.ID, t.CustName, t.Country,f.Val,(f.ID-1)/3 AS Slot,
ROW_NUMBER() OVER (PARTITION BY  t.ID, t.CustName, t.Country,(f.ID-1)/3 ORDER BY f.ID) AS Rn
FROM table t
CROSS APPLY dbo.ParseValues(t.Cities,' ')f
)t
GROUP BY ID,CustName,Country,Val,Slot

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2012-02-13 : 16:22:08

Thanks. I created the function and used the above query exactly as it is. The only change that i did was renamed the table to Table_1 --> The input table that has the values.

Just for 2 input records the query ran for 1 hour and 35 minutes. So i had to cancel it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-13 : 17:03:02

Try this

SELECT		ID,
		CustName,
		Country,
		MAX(CASE WHEN DataRow % 3 = 0 THEN Val END) AS City,
		MAX(CASE WHEN DataRow % 3 = 1 THEN Val END) AS [State],
		MAX(CASE WHEN DataRow % 3 = 2 THEN Val END) AS ZIP
FROM		(
			SELECT		t.ID,
					t.CustName,
					t.Country,
					f.Val,
					f.ID - 1 AS DataRow
			FROM		dbo.Table AS t
			CROSS APPLY	dbo.ParseValues(t.Cities, ' ') AS f
		) AS d
GROUP BY	ID,
		CustName,
		Country,
		DataRow / 3

N 56°04'39.26"
E 12°55'05.63"

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2012-02-13 : 19:11:03

Hi SwePeso,
I tried the query that you provided. It still was running for 1 hour and 30 mins and i cancelled it. I even indexed the columns. Looks like the CROSS APPLY takes a long time.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-14 : 03:23:49

I don't think so.
I can run 10,000 rows with a number of cities between 1 and 9 in less than 4 seconds on my laptop.
On the other hand, I am using my own version ( fnParseList ) of the split function found here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources