Need to isolate string portion

Question

Need to isolate string portion

cdun2

SSCertifiable

Points: 7893
More actions
June 18, 2003 at 12:18 pm

#82773

Hello,
I have a field that contains street names, a sample of which could be expressed as follows:
Bailey ST
Barton STREET
Beck RD
Birch WAY
Bonita Road
I need to isolate the suffix of the street names (ST, STREET, RD, WAY, Road) and remove them from the data (I have a 'suffix' field in which I used UPDATE statments to populate the field with a designated street name suffix).
In every case the suffix is at the end of the street name, and is preceeded with a space. The length of the suffix varies.
Is there a way I could use RIGHT with SUBSTRING and LEN to achieve this, or do I need to take another approach?
Thanks!
CSDunn

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1



-- St and Rd first

UPDATE MyTable

SET Address = SUBSTRING(AddressField, LEN(RTRIM(AddressField))-3, LEN(RTRIM(AddressField)))

WHERE RIGHT(RTRIM(AddressField, 3)) = ' St'

OR RIGHT(RTRIM(AddressField, 3)) = ' Rd'

I think you could use the same technique for the rest of the fields...

cquel1 SSC Rookie Points: 29 More actions · Answer 2

@test-2='Barton STREET'

select ltrim(rtrim(reverse(left(reverse(@test),charindex(' ',reverse(@test))))))

here @test-2 is your field.

I just wrote the logic. You can modify the result accordingly.

note: this logic works even if you have space in street name (like barton springs RD).

jpipes SSChampion Points: 12230 More actions · Answer 3

Just be careful of addresses like "13 Barton Street #200". My solution would leave these types of records unaffected and rely on either a more complex where condition, or manual updates...

cdun2 SSCertifiable Points: 7893 More actions · Answer 4

cdun2

SSCertifiable

Points: 7893

June 18, 2003 at 2:27 pm

#461998

Thanks, this is very helpful!

CSDunn

Kathy-82899 Right there with Babe Points: 762 More actions · Answer 5

You might also try this:

Select Left(Address,Len(Address) - CharIndex(' ',Reverse(Address)))

cdun2 SSCertifiable Points: 7893 More actions · Answer 6

Thanks, this is great!

quote:
Select Left(Address,Len(Address) - CharIndex(' ',Reverse(Address)))

gljjr SSChampion Points: 10080 More actions · Answer 7

I would probably create a table to hold the values you want to replace and then do something like the following...



UPDATE t

SET Addr = SUBSTRING(t.Addr, LEN(t.Addr) - LEN(S.Streetname), LEN(S.Streetname)) + ' ' + S.Streetname

FROM Table t

     JOIN Streetnames S ON right(t.Addr,LEN(s.Streetname) = s.Streetname

As stated before you really need to be careful about the replacement as some of the words you would replace may leave you with incorrect data.

Gary Johnson

DBA

Sr. DB Engineer

Edited by - gljjr on 06/19/2003 5:39:28 PM

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.