How to select rows which start with digit in PostgreSQL?

Question

Need to get rows starting with digit e.g. '1test', '32 test'. I tried

SELECT * FROM table WHERE name LIKE '[0-9]%'

as I used to do in MSSQL but it wasn't successful.

Answer 1

Try this:

SELECT * FROM table WHERE name ~ '^[0-9]'

This uses a POSIX regular expression.

Answer 2

According to the docs, you can use SIMILAR TO instead of LIKE to do regex-like matching, and ~ to do full POSIX regex matching.