August 2, 2022 at 8:59 pm
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 2, 2022 at 9:08 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
August 2, 2022 at 9:26 pm
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
Change is inevitable... Change for the better is not.
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