Sql: search column that starts with digits

Question

I'm having trouble with the sql below. Basically I have rows that contains strings according to the format: 129&c=cars. I only want the digits part, e.g. 129. The sql query is:

$result = mysql_query("SELECT * FROM " . $db_table . " WHERE id LIKE '" . $id . "%'");

Why doesn't % work? I can't use %...% because it catches too much.

Answer 1

I would actually recommend using regular expressions fo the matching, but unfortunately, there is no way to capture the matching part with mysql. You will have to do the extraction in php. If you have an array containing all the results called $array:

$array = preg_replace('/^(\d+).*/', '$1', $array);

Answer 2

You can use the MySQL 'regexp' stuff in the WHERE clause to reduce the amount of data retrieved to just the rows you want. The basic for of your query would look like:

SELECT * FROM table WHERE field REGEXP '^$id&'

where $id is inserted by PHP and the data you want is always at the start of the field and followed by a &. If not, adjust the regex to suit, of course.

MySQL's regex engine can't do capturing, unfortunately, so you'll still have to do some parsing in PHP as soulmerge showed above, but with the 'where regexp' stuff in MySQL, you'll only have to deal with rows you know contain the data you want, not the entire table.

Answer 3

Using a query like this:

SELECT  *
FROM    mytable
WHERE   id >= '0' COLLATE UTF8_BIN
        AND id < ':' COLLATE UTF8_BIN

will return all strings that start with a digit and make your expression sargable, i. e. and index on id can be used.

This will make your query run faster.