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 |
|
spsubaseball
Starting Member
17 Posts |
 Posted - 2012-04-27 : 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 |
|
|
spsubaseball
Starting Member
17 Posts |
Posted - 2012-04-27 : 14:55:55
|
| Does Anyone have any suggestions? I'm lost. |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-04-27 : 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/) |
|
|
|
vinu.vijayan
Posting Yak Master
227 Posts |
Posted - 2012-04-28 : 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
52326 Posts |
Posted - 2012-04-28 : 19:16:38
|
| one hint is you can use SUBSTRING,PATINDEX,CHARINDEX functions, but for that also format should be consistent------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Best Way to split a column into multiple columns