I'm looking to isolate street names from addresses by removing the building number from the start of the address. In some cases, there is no building number, so the address begins with a numbered street. The query needs to be used in a view.
e.g.
| Order Key | Address |
|---|---|
| 1 | 1990 bismuth ave. |
| 2 | 61st st. |
to:
| Order Key | Street Name |
|---|---|
| 1 | bismuth ave. |
| 2 | 61st st. |
My initial attempt was to use string_split to separate the address by spaces, then select only those rows containing an alphabetic character, then to aggregate them back together grouping over their shared order key.
The problem I'm running into is formatting this into a sequence that doesn't involve using one or more ctes, or nested selects with an aggregate, both of which are throwing errors. This is the version with CTEs, which works, but I'm trying to create this street name column within the view select statement:
WITH street_name_cte AS (
SELECT [ORDER_KEY]
,[Address]
,value as SPLIT_STRING
FROM [TABLE]
CROSS APPLY STRING_SPLIT([Address], ' ')
WHERE value LIKE '%[abcdefghijklmnopqrstuvwxyz]%'
)
SELECT string_agg(SPLIT_STRING, ' ')
FROM street_name_cte
GROUP BY [ORDER_KEY]
This gets the results I need, but I'd like to find a way to format it to add the cleaned street name column to an existing table without ctes in order to include it in-line in a larger select statement which creates a view.
