CODEWITHSUNDEEP
mysqlsqldatabase
mister martin
mister martin

Reputation: 6252

select where the first character is numeric

I am creating a query that should return title's that begin with 0-9 or a-z, depending on what is requested. I have an index on the title column so for the individual letters I'm doing:

SELECT * FROM table WHERE title LIKE 'a%';
SELECT * FROM table WHERE title LIKE 'b%';
SELECT * FROM table WHERE title LIKE 'c%';
/* etc... */

For the numbers, however, it doesn't really matter which number it is, it only matters that the first character is numeric. I want something like:

SELECT * FROM table WHERE title LIKE '0%'
                       OR title LIKE '1%';
                       OR title LIKE '2%';
                       OR title LIKE '3%';
/* etc... */

But this feels a little sloppy. To my understanding regex won't really work well with an index, and I'm not sure about string functions. Would something like WHERE SUBSTRING(title,1,1) IN (0,1,2,3,4,5,6,7,8,9) still take advantage of the index or is there a more compact way to do this?

Upvotes: 1

Views: 2897

Answers (2)

Andreas Wederbrand
Andreas Wederbrand

Reputation: 40061

@Gordon is correct in that all ascii strings are between '0' and ':'

The same is true for the queries with letters, all string between 'a' and '{' will match.

Regarding the second part of your question, if WHERE SUBSTRING(title,1,1) IN (0,1,2,3,4,5,6,7,8,9) will use an index, the answer is no. When the column from the index is used in a function, SUBSTRING in this case, it has to apply the function to all values in the table, always making a full table scan.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

If your characters are all "ASCII", then you can do:

where title >= '0' and title < ':'

This should be able to use an index.

I admit the meaning is a bit elusive, but the colon is the first ASCII character after '9'.

Upvotes: 5

Related Questions