CODEWITHSUNDEEP
mysqlsql
haneulkim
haneulkim

Reputation: 4928

using wildcard to select names that starts with specific character not working

I am trying to use wildcard to select all names in first_name column that starts with 'b','s', or 'p' by using 

SELECT first_name
from actor
where first_name like '[bsp]%';

Upvotes: 1

Views: 86

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

The simplest method is a regular expression:

where first_name regexp '^[bsp]';

Unlike like patterns, regular expressions do not need to match the entire expression. regexp returns true if the pattern matches anywhere. Hence, the equivalent of % is not needed (which would be .* in a regular expression).

You could also use:

where left(first_name, 1) in ('b', 's', 'p')

Upvotes: 0

FanoFN
FanoFN

Reputation: 7114

This work as well:

SELECT first_name FROM actor WHERE first_name REGEXP '^b|^s|^p';

Tested on MySQL 4.1 and MariaDB 10.3.10

Upvotes: 0

Hong Van Vit
Hong Van Vit

Reputation: 2976

I think it should be: 

SELECT first_name
from actor
where first_name like 'b%' or first_name like 's%' or first_name like 'p%';

Upvotes: 0

esqew
esqew

Reputation: 44699

You haven't specified which version of MySQL you're using, which changes the level of support for regular expressions. A backward-compatible version (back to MySQL ~v5.x) uses:

  1. The regular expression pattern .* in place of the MySQL % wildcard

  2. The regexp token to declare that the condition should be interpreted as a regular expression

SELECT first_name from actor where first_name regexp '[bsp].*?';

SQLFiddle

Upvotes: 2

Related Questions