January 14, 2007 at 4:32 pm
I have a property database and need to strip out the 2nd Address component from the address column when it exists. If there are two address components in the field, the address column will have a ',' then the Address2 component like 'Unit# 7'. But many records don't have a 2nd address component.
The data looks like:
123 Main
234 Main Street, Unit# 7
236 Main Street, Suite 6
545 Elm
I am trying to split the address into its two components. Address and Address2.
I can use the following select statement:
SELECT RTRIM(SUBSTRING(Address,1,CHARINDEX(',',Address,1)-1)) AS Name from MLSProperty
but this only works if all the rows have the second component after a comma.
Any help would be appreciated.
January 14, 2007 at 5:15 pm
check and see if the address has a comma first and then parse...
something along these lines...
SELECT
CASE
WHEN CHARINDEX(',', Address) > 0 THEN RTRIM(SUBSTRING(Address,1,CHARINDEX(',',Address,1)-1))
END Name
Are you sure there aren't any instances where there's more than 1 comma ?!?!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply