Solution for String Calculation

• cdun2

  SSCertifiable

  Points: 7893

  More actions

  June 19, 2003 at 3:38 pm

  #81845

  Hello,

  I thought I would pass along a solution that, thanks to several contributors, works very nicely for removing a street suffix from a street name:

  ****************************************************

  UPDATE tblSBStreetData

  SET StreetName =

  LEFT(StreetName,LEN(StreetName) - CHARINDEX(' ',REVERSE(StreetName)))

  WHERE

  streetname like '% RD'

  or streetname like '% DR'

  or streetname like '% ST'

  or streetname like '% AVE'

  or streetname like '% PL'

  or streetname like '% WAY'

  or streetname like '% CT'

  or streetname like '% ST'

  or streetname like '% LN'

  or streetname like '% CIR'

  or streetname like '% Drive'

  or streetname like '% Street'

  or streetname like '% Road'

  or streetname like '% Circle'

  or streetname like '% Avenue'

  or streetname like '% Blvd'

  or streetname like '% Exp.'

  *****************************************

  Thanks again for your help!

  CSDunn

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736494

  More actions

  June 20, 2003 at 3:29 pm

  #462478

  Thanks for the post.

  Steve Jones

  sjones@sqlservercentral.com

  http://www.sqlservercentral.com/columnists/sjones

  http://www.dkranch.net