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

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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]'

Method 2

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.

Method 3

Try this:

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

Method 4

One can also use RLIKE as below

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

Method 5

Try this simple select:

select * 
from artists 
where name like "a%"

Method 6

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');

Method 7

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating