CODEWITHSUNDEEP
mysql
Salil
Salil

Reputation: 47482

find all the name using mysql query which start with the letter 'a'

I want to find the data from table artists where name is start with letter a, b, c.

i.e. 
My o/p should contain
Adam
Alan
Bob
Ben
Chris
Cameron

But not GlAin, doC, dolBie

Upvotes: 35

Views: 152148

Answers (8)

jarlh
jarlh

Reputation: 44766

I'd assume

SELECT * FROM artists WHERE name >= 'a' and name < 'd'

will perform good, as long as name is indexed.

Upvotes: 0

mike
mike

Reputation: 421

In MySQL use the '^' to identify you want to check the first char of the string then define the array [] of letters you want to check for.

Try This

SELECT * FROM artists WHERE name REGEXP '^[abc]'

Upvotes: 39

Mohammed Jafar
Mohammed Jafar

Reputation: 543

Try this simple select:

select * 
from artists 
where name like "a%"

Upvotes: 7

curious visitor
curious visitor

Reputation: 21

try using CHARLIST as shown below:

select distinct name from artists where name RLIKE '^[abc]';

use distinct only if you want distinct values only. To read about it Click here.

Upvotes: 2

akostre
akostre

Reputation: 21

I would go for substr() functionality in MySql.

Basically, this function takes account of three parameters i.e. substr(str,pos,len)

http://www.w3resource.com/mysql/string-functions/mysql-substr-function.php

SELECT * FROM artists 
WHERE lower(substr(name,1,1)) in ('a','b','c');

Upvotes: 2

madhu131313
madhu131313

Reputation: 7386

One can also use RLIKE as below

SELECT * FROM artists WHERE name RLIKE '^[abc]';

Upvotes: 13

Dienow
Dienow

Reputation: 1415

You can use like 'A%' expression, but if you want this query to run fast for large tables I'd recommend you to put number of first button into separate field with tiny int type.

Upvotes: 29

Dumb Guy
Dumb Guy

Reputation: 3446

Try this:

select * from artists where name like "A%" or name like "B%" or name like "C%"

Upvotes: 22

Related Questions