Extracting first three words from Address field

• Kin16

  SSC Enthusiast

  Points: 177

  More actions

  August 2, 2022 at 8:59 pm

  #4071602

  I am trying to standardize an address field.  I want to extract the first three words of the full address (ex: 123 Main Street) into a new column.  I am using the code below, which is not working.  Any insight would be appreciated.  Thank you!

  set [FIRST_THREE_WORDS] = SUBSTRING(ADDRESS, 0, CHARINDEX(' ', ADDRESS, CHARINDEX(' ', ADDRESS, CHARINDEX(' ', ADDRESS, 0)+1)+1)) FIRST_THREE_WORDS

  FROM [ADDRESSES]

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  August 2, 2022 at 9:08 pm

  #4071603

  SELECT [FIRST_THREE_WORDS] = LEFT(ADDRESS + ' ', CHARINDEX(' ', ADDRESS + ' ', CHARINDEX(' ', ADDRESS, CHARINDEX(' ', ADDRESS + ' ', 0)+1)+1))

  FROM ( SELECT '123 Main Street' ) AS [ADDRESSES](Address)

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  August 2, 2022 at 9:26 pm

  #4071607

  Kin16 wrote:

  I am trying to standardize an address field.  I want to extract the first three words of the full address (ex: 123 Main Street) into a new column.  I am using the code below, which is not working.  Any insight would be appreciated.  Thank you!

  set [FIRST_THREE_WORDS] = SUBSTRING(ADDRESS, 0, CHARINDEX(' ', ADDRESS, CHARINDEX(' ', ADDRESS, CHARINDEX(' ', ADDRESS, 0)+1)+1)) FIRST_THREE_WORDS FROM [ADDRESSES]

  That's going to lead to your own personal bit of hell.  Consider addresses like the following.

  123 South East New London Road

  18232 E. via Rimini Rd

   

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)