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 |
|
nt4vn
Yak Posting Veteran
98 Posts |
 Posted - 2011-10-06 : 13:43:51
|
| I have table that contained the following fields:CITY_STATE (city and state value, such as ALLENTOWN PA 19066-111)Note: some of CITY_STATE field only show information of city and state, not zip code, but some some all (city, state, zip) for that field (i.e: NEWTOWN SQ PA ) STATE (state value, such as: PA)ZIP (contained 9 character for zip code, such as 19066-111)I don't know how I write sql statement to select CITY_STATE field to display only city information. As above example, I want to display only "ALLENTOWN' for city_state field. Or another example, "SEVEN VALLEYS PA 17360-908", I want to result show up "SEVEN VALLEYS" for city_state field.Thanks, |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-06 : 13:55:45
|
| that's difficult to shred only city information unless you've a consistent format in the way city and state values.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
nt4vn
Yak Posting Veteran
98 Posts |
Posted - 2011-10-06 : 14:21:04
|
It's not consistent format...that was why I have difficulty to do it...Thanks,quote:
Originally posted by visakh16
that's difficult to shred only city information unless you've a consistent format in the way city and state values.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-06 : 14:29:52
|
| then you've to find all possible formats and do it one at a time for each set------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nt4vn
Yak Posting Veteran
98 Posts |
Posted - 2011-10-06 : 14:48:17
|
I tried to go through the result of query and using replace function to replace some line contain city and zip....That is only option I can do now....thanks,quote:
Originally posted by visakh16
then you've to find all possible formats and do it one at a time for each set------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-07 : 01:39:16
|
| i think thats only possible way.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
sql question