Z-Car

MySQL – Select rows from a table when a field starts with a number

girl drawing database

A common request is how to select records from a table when a specific field starts with a number. This request often is used when searching free-form text fields where the fields were used to provide an answer field to a question.

The simplest way to attack this is by using a regular expression. For example:

SELECT * FROM BadlyDesignedTable WHERE AnswerColumn regexp ‘^[0-9]+’;

or
SELECT * FROM BadlyDesignedTable WHERE AnswerColumn RLIKE ‘^[0-9]+’;

Regex and RLIKE are compatible keywords that both represent regular expression matching.

The regex is described as follows :

^    – Start anchor, used to ensure the pattern matches start of the string.
[    – Start of character class.
0-9  – Any digit
]    – End of character class

One thought on “MySQL – Select rows from a table when a field starts with a number

  1. Pingback: MySQL Delimiters – Or why I hate stored procedures and Error Code : 1064 You have an error in your SQL syntax. | Prodromus

Leave a Reply and Let me know what you think...