sqlsnowflake-cloud-data-platform

Regex on snowflake to find value ends with '_' + number

I would like to return the table name with the format ends with '_'+ number e.g. 'Sales_123', 'Employee_999999

I tried several format but just don't work on snowflake....

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%^_[0-9]$' ESCAPE '^'

Based on the knowledge I have, I know ^ here is for special char, _ to make it real character and it need to be use with escape ^ in the end

The second, $ means end of the value.

I may be very wrong, please let me know how it works correctly.

Solution

  • You have to use rlike function for regex-based string searches. It is not possible with the like function

    Documentation for your reference https://docs.snowflake.com/en/sql-reference/functions/rlike

    select distinct table_name
from information_schema.tables
where rlike(table_name, '\\w+_[0-9]+');

    select count(distinct table_name)
from information_schema.tables
where table_name rlike '\\w+_[0-9]+';