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.
Author |
Topic |
godspeedba
Yak Posting Veteran
90 Posts |
Posted - 2013-06-19 : 04:41:34
|
I have an old system where the address in its entirety is stored in one field.I need to develop a solution where I need to split the field into subsequent fields:addresssplit toaddress1address2towncountyThe address solution is comma separated, but I have never used any split functions in SQL, do they even exist? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
godspeedba
Yak Posting Veteran
90 Posts |
Posted - 2013-06-19 : 05:06:58
|
Thanks for this, how would I go about putting them in the fields. As far as I can see there no way of referencing the split words directly. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 05:11:27
|
there are waysfor example something likeSELECT t.Address,MAX(CASE WHEN ID=1 THEN Val END) AS [Address1],MAX(CASE WHEN ID=2 THEN Val END) AS [Address2],MAX(CASE WHEN ID=3 THEN Val END) AS [Town],MAX(CASE WHEN ID=4 THEN Val END) AS [county]FROM YourTable tCROSS APPLY dbo.ParseValues(t.Address,',')fGROUP BY t.Address provided the format in address field data is consistentParseValues definition is herehttp://visakhm.blogspot.com/2010/02/parsing-delimited-string.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
godspeedba
Yak Posting Veteran
90 Posts |
Posted - 2013-06-19 : 05:14:34
|
Visakh, once again, YOU ARE THE BEST |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 05:15:08
|
for putting it into fields useUPDATE tSET t.Address1 = t1.Address1,t.Address2 = t1.Address2,t.Town = t1.Town,t.county = t1.countyFROM table tCROSS APPLY (SELECT MAX(CASE WHEN ID=1 THEN Val END) AS [Address1],MAX(CASE WHEN ID=2 THEN Val END) AS [Address2],MAX(CASE WHEN ID=3 THEN Val END) AS [Town],MAX(CASE WHEN ID=4 THEN Val END) AS [county]FROM dbo.ParseValues(t.Address,','))t1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 05:16:55
|
quote: Originally posted by godspeedba Visakh, once again, YOU ARE THE BEST
you're welcome I find myself as a noble follower of "real experts" here ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-19 : 05:37:50
|
If you doesn't have any . (dot symbol) in the address field, then you can use this approachSELECT PARSENAME( REPLACE( Address, ',', '.'), 1) [Address1],PARSENAME( REPLACE( Address, ',', '.'), 2) [Address2],PARSENAME( REPLACE( Address, ',', '.'), 3) [Town],PARSENAME( REPLACE( Address, ',', '.'), 4) [Country]FROM TableName --Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 05:45:07
|
quote: Originally posted by bandi If you doesn't have any . (dot symbol) in the address field, then you can use this approachSELECT PARSENAME( REPLACE( Address, ',', '.'), 1) [Address1],PARSENAME( REPLACE( Address, ',', '.'), 2) [Address2],PARSENAME( REPLACE( Address, ',', '.'), 3) [Town],PARSENAME( REPLACE( Address, ',', '.'), 4) [Country]FROM TableName --Chandu
this will only work if it has a maximum of 3 commas (ie total of 4 value components). if it has more parts this will fail which is why i dont prefer this Also if there are existing . characters within address like st., jn. etc that will also cause result to skew------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-19 : 05:49:37
|
quote: Originally posted by visakh16Also if there are existing . characters within address like st., jn. etc that will also cause result to skew
I too agree visakh... that's why I kept that limitation also...--Chandu |
|
|
|
|
|
|
|