Have a table containing address details with Cities for which some contain numbers that indicate the City district:

Would like to extract all Cities that contain numbers but excluding Praha.

Tried a combination of WHERE with LIKE and NOT LIKE or != as well as subquery excluding Praha or entire Country 'CZE' but always end up with all the values containing numbers.

Thanks!

CodePudding user response：

You can use:

SELECT *
FROM   table_name
WHERE  REGEXP_LIKE(city, '\s \d $')
AND    city NOT LIKE 'Praha%'

Or, from Oracle 12, without (slow) regular expressions:

SELECT *
FROM   table_name
WHERE  TO_NUMBER(
         SUBSTR(city, INSTR(city, ' ', -1))
         DEFAULT NULL ON CONVERSION ERROR
       ) IS NOT NULL
AND    city NOT LIKE 'Praha%'

Which, for the sample data:

CREATE TABLE table_name (ID, CITY, COUNTRY) AS
SELECT 1, 'Praha 4',    'CZE' FROM DUAL UNION ALL
SELECT 2, 'Dublin 1',   'GBR' FROM DUAL UNION ALL
SELECT 3, 'Budapest 3', 'HUN' FROM DUAL UNION ALL
SELECT 4, 'Dublin',     'GBR' FROM DUAL UNION ALL
SELECT 5, 'Praha 7',    'CZE' FROM DUAL UNION ALL
SELECT 6, 'Budapest',   'HUN' FROM DUAL;

Both output:

ID	CITY	COUNTRY
2	Dublin 1	GBR
3	Budapest 3	HUN

db<>fiddle here