| Author |
 Topic  |
|
|
spsubaseball
Starting Member
17 Posts |
 Posted - 04/27/2012 : 12:56:40
|
select address, Street_No, Street_Prefix, Street_Name, Street_Type from may_TitleR1 where ProcessedYN='N' and Property_Id = '0'
Above select statement has a string in address; I'm trying to split the column into separate columns and update them into the appropriate columns. Can anyone help?
Address Example: 1226 Pinedale Cir NW |
Edited by - spsubaseball on 04/27/2012 13:08:36
|
|
|
spsubaseball
Starting Member
17 Posts |
Posted - 04/27/2012 : 14:55:55
|
| Does Anyone have any suggestions? I'm lost. |
 |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/27/2012 : 15:36:46
|
Nobody is responding probably because this is a task made difficult by the variations and unpredictability in the address format. As you noted, it can have a number to start with or it could be a number and a letter (1226 or 1226B), the street name could be one, two or more words (Pinedale or George Washington), may or may not end with N,S,NW etc. and so on.
If there was a predictable pattern to it, it can be parsed, but without that, the results would be very unreliable.
On the other hand, if you simply wanted to split your example into 4 tokens, that is easily done. Look up string splitters (http://www.sqlservercentral.com/articles/Tally+Table/72993/) |
Edited by - sunitabeck on 04/27/2012 15:37:52 |
|
|
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 04/28/2012 : 07:29:49
|
My magic crystal Ball isn't working today, so I can't see from where all your selected columns are coming from, neither can i see the table.
So, if you can understand the grave problem I am stuck in right now.....please post the DDL of the table and some sample data so everyone can have a look at what you want.
N 28° 33' 11.93148"
E 77° 14' 33.66384" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48119 Posts |
Posted - 04/28/2012 : 19:16:38
|
one hint is you can use SUBSTRING,PATINDEX,CHARINDEX functions, but for that also format should be consistent
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Best Way to split a column into multiple columns
