~ operator in SQLITE

Question

I'm running this part of a query on Postgres and it's running fine

where column ~ '^[0-9]'

But when I try to run it in SQLite, I get this error:

near "~": syntax error

Do you have any idea how I can run this function in SQLite?

Answer 1

Actually, if you want columns that start with a digit, you can simply use:

where substr(column, 1, 1) between '0' and '9'

SQLite doesn't have native support for regular expressions -- although it is really easy to extend. It does use support Unix globbing, but in this case you can use the built-in functions.

Answer 2

SQLite supports GLOB operator:

WHERE column GLOB '[0-9]*'

[0-9] means that the value starts with a numeric digit and * means that may follow any character(s).