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 |
|
george909
Starting Member
4 Posts |
 Posted - 2011-06-02 : 14:21:28
|
| I need some support with attempting to parse an [Address] field in a SQL table. I need to extract the [House#], [Direction] (if any), [StreetName], and [StreetType].I was able to extract the [HOUSE#] But seem to be challenged with attempting to do the rest. Below is an example:[Address]1234 N BRUSH ST100 S. MAIN143121 BUENA VISTA AVEI am seeking to get the following results.[HOUSE]1234100143121[Direction]NS.NULL[StreetName]BRUSHMAINBUENA VISTA[StreetType]STNULLAVEFor the [House] field I have the following code:SUBSTRING([ADDRESS],1,(CHARINDEX(' ',[ADDRESS])-1)) AS "HOUSE"But the rest is challenging due to the direction only certain times in the records. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-06-02 : 14:39:31
|
| There really isn't a good way to do this, there are too many possiblities to consider.For example,1234 Martin Luther King, Jr. Blvd would be really hard to split out. I know that's helpful to you, but this is why table design should be thought before the data is put in to it!JimEveryday I learn something that somebody else already knew |
 |
|
|
|
|
|
Parsing Variations of a Address String Field