Making an SQL Aggregate ignore Numbers, is it possible?-CodePudding

I'm looking at calls to different stores and wanted to see if it is possible to make my aggregate ignore numbers. There are multiple listings for the same store based on the worker who picked up the phone. Such as Phoenix 1001, Phoenix 1002, Phoenix 1003. All three are the phoenix location but listed as 3 different stores because of the numbers at the end.

CodePudding user response：

Try using REGEXP_SUBSTR

select regexp_substr(col1,'\\D*') sol1
from (values('Phoenix 1001'),('Phoenix 1002'),('Phoenix 1003')) as tbl (col1);

CodePudding user response：

SELECT data
FROM your_table
WHERE regexp_like(column_name, 'Phoenix')

Selects data for columns having Phoenix string.

WITH w as (
 SELECT columns_you_want, 
   regexp_substr(location_column, '[^0-9 ] ') as location
)

SELECT data
FROM w
GROUP BY w.location

Selects data you want. You may add aggregates.