J Thomas Beaton J Thomas Beaton - 6 months ago 64
SQL Question

iN VERTICA, WHAT EXPRESSION WILL OMIT RESULTS THAT CONTAIN ANY NUMERAL?

I need to query a vertica database where addresses are stored and get results where the varchar contains no numbers.

for MSSQL someone suggested:

SELECT * FROM Table WHERE Column NOT LIKE '%[0-9]%'


but this is not working in vertica, meaning I get returned every address with and without a number in the address1 field. I could do the below, except it is clunky:

select * from [TABLE_NAME] where [FIELD] NOT like '%0%'
and [FIELD] NOT like '%1%'
and [FIELD] NOT like '%2%'
and [FIELD] NOT like '%3%'
and [FIELD] NOT like '%4%'
and [FIELD] NOT like '%5%'
and [FIELD] NOT like '%6%'
and [FIELD] NOT like '%7%'
and [FIELD] NOT like '%8%'
and [FIELD] NOT like '%9%';


Is there a clean expression that can be used in vertica that is similar to the statement for MSSQL?

Answer

You are using SQL Server LIKE syntax. Use regular expressions instead:

where not regexp_like(column, '[0-9]')

Or, alternatively:

where regexp_like(column, '^[^0-9]*$')