I have an address dataset with about 6000 rows, and one column containing the full address ("Full_Address"). The column contains the address in the following format: street address, city/community, zip (ex. 7257 N WYOMING RD WYOMING, 61491). I want the address to include just the street address (ex. 7257 N Wyoming RD). I have another column for Community(ex. Wyoming). I've been using the Community column to extract the address. The code is given below:
CASE WHEN Community NOT IN ('','None','<Null>','0')
THEN SUBSTRING(Full_Address,0,CHARINDEX(Community,Full_Address))
This code works well when the street name and the community name are NOT the same, but when they are (as in the example above), it gives me the following output: 7257 N. Is there a way I can specify that if the community name occurs twice in the address, to index to the second occurrence instead of the first? I can't use any other column to extract the address because the "Full_Address" column is the only one containing that information. I also can't use CHARINDEX to look for ',' because some of the columns don't have a comma separating the street address from the community (as in the example).
Thank you!
I think that for more accurate results in case someone will add address without the comma you should search from the end, like this:
LEFT(Full_Address , LEN(Full_Address) - (CHARINDEX(REVERSE(Community),REVERSE(Full_Address))+LEN(Community)))
You can test it with this code on your SSMS:
declare @Full_Address as nvarchar(100)
set @Full_Address = '7257 N WYOMING RD WYOMING, 61491'
declare @Community as nvarchar(100)
set @Community = 'WYOMING'
select LEFT(@Full_Address , LEN(@Full_Address) - (CHARINDEX(REVERSE(@Community) ,REVERSE(@Full_Address))+LEN(@Community)))