In Databricks, you can use the like operator or rlike operator (for regular expressions) to filter strings based on patterns containing numbers from 0 to 9.

LIKE Operator

The like operator matches patterns literally, except for the following special characters:

• _: Matches any single character.
• %: Matches zero or more characters.

Examples

SELECT *
FROM table_name
WHERE column_name LIKE '%0%'    -- Contains any number
   OR column_name LIKE '1_'     -- Starts with 1 and has one more character
   OR column_name LIKE '_9'     -- Ends with 9 and has one preceding character
   OR column_name LIKE '%[0-9]%' -- Contains at least one number

RLIKE Operator (Regular Expressions)

The rlike operator matches patterns using regular expressions. This allows for more complex patterns.

Examples

SELECT *
FROM table_name
WHERE column_name RLIKE '^[0-9]'    -- Starts with a number
   OR column_name RLIKE '[0-9]$'    -- Ends with a number
   OR column_name RLIKE '.*[0-9].*' -- Contains a number anywhere

Your Data

Based on the data you provided (“db01”, “db9”, “dbXYZ”), if you want to filter based on the presence of numbers 0 to 9, the following queries would work:

SELECT *
FROM my_data
WHERE my_column LIKE '%[0-9]%'  -- Using LIKE
   OR my_column RLIKE '.*[0-9].*' -- Using RLIKE

This would give you the result:

0   db01
1    db9