Extracting first three words from Address field

Question

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]
August 5, 2022 at 9:08 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

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

ScottPletcher SSC Guru Points: 101097 More actions · Answer 1

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: 1004339 More actions · Answer 2

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)