I have a table with a lot of names followed by a number. All entries use the same syntax, but some names have numbers from 1 up to 99
example:
john 1
john 2
john 3
john smith 1
john smith 2
In this example, i'm trying to select all "john" entries
SELECT * FROM my_table WHERE name LIKE 'john %'
The problem is that it will also select entries from "john smith". How can i avoid that ? Is it possible to use some kind of wildcard to do something like that:
SELECT * FROM my_table WHERE name LIKE 'john [0-9]'
Using REGEXP it seems the simplest query would be:
select * from tablename where name REGEXP '^john smith [[:digit:]]{1,2}$';
which will limit the expression to having at most 2 digits, thus effectively limiting it to 0-99.
Or
select * from tablename where name REGEXP '^john smith [[:digit:]]+$';
which won't limit it to 0-99 but will allow any combination of digits after the space.
Note that if you don't include the ^ at the beginning then things like "x john smith 2" would be allowed. And if you don't include the $ at the end, then "john smith 2 x" would be allowed.
To capture both john and john smith would require something more like:
select * from tablename where name REGEXP '^john [[:alpha:]]{0,}[[:space:]]{0,1}[[:digit:]]{1,2}$' ;
The {n,n} is the min and max times for repeating the element. So for "john" we will not have this alpha element but for "john smith" we will have one set of alpha letters, hence the {0,} meaning minimum 0 alpha characters and no max. The same for space since with "john" we won't have this space. But probably we want to limit the number of spaces that can occur here to 1, hence the max part is included {0,1}.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments