Numeric pattern in LIKE query?
I have a table with a lot of names followed by a number. All entries use the same syntax, but some names are numbered from 1 to 99
Example:
john 1
john 2
john 3
john smith 1
john smith 2
In this example I am trying to select all records "john"
SELECT * FROM my_table WHERE name LIKE 'john %'
The problem is that it will also select records from "john smith". How can I avoid this? Is it possible to use some kind of wildcard to do something like this:
SELECT * FROM my_table WHERE name LIKE 'john [0-9]'
source to share
Using REGEXP seems to be the simplest query:
select * from tablename where name REGEXP '^john smith [[:digit:]]{1,2}$';
which limits the expression to at most 2 digits, thus effectively limiting it to 0-99.
or
select * from tablename where name REGEXP '^john smith [[:digit:]]+$';
which will not limit it to 0-99, but will allow any combination of numbers after the space.
Note that if you don't include ^
at the beginning then things like "x john smith 2" will be allowed. And if you don't include $
in the end, then "john smith 2 x" is allowed.
Capturing both john and john smith requires more:
select * from tablename where name REGEXP '^john [[:alpha:]]{0,}[[:space:]]{0,1}[[:digit:]]{1,2}$' ;
{n,n}
is the minimum and maximum repetition time of the element. So for "john" we will not have this alpha element, but for "john smith" we will have one set of alpha letters, so it {0,}
will mean at least 0 alpha characters and not max. Ditto for space, since with "john" we won't have that space. But we probably want to limit the number of spaces that can occur here to 1, so the maximum part is included {0,1}
.
source to share
There LIKE
is nothing like this for, but you can useREGEXP
You will probably get better performance:
SELECT *
FROM my_table
WHERE name LIKE 'john %'
AND 0 < SUBSTR(name,6)
This will convert the second part of the string to a number and if it is greater than 0, anything that starts with a letter will be converted to 0.
LIKE in this case can still use an index on name
.
I would definitely question your data structure, the number after the name should really be in a separate numeric column. Think about it, the last name "Smith" should be in a separate column too!
source to share