MySQL database & the alphabet!

is this possible?

[dreamcatcher]

Hi guys,

Lets say I have an x amount of file names in a database and I just want to fetch all those that begin with the letter 'a', is that possible? I know I can fetch the whole set in alphabetical order, but what if I want to restrict it to a certain letter?

All the file names are lower case, so capital letters won`t be necessary.

Thanks. :)

[RussellC]

SELECT * FROM Table_Name WHERE last_name LIKE 'a%';

[dreamcatcher]

Thanks RussellC. And that will only fetch entries beginning with the letter 'a' and not all entries that contain the letter 'a'? If I get the info and then convert it using substr() would that work?

SELECT filename FROM table

$var = substr($row['filename'], 0, 1);

if ($var=="a")
do something
else if ($var == "b")
do something else

Or is there a better way?

[justageek]

You could do something like this if all you want is the first character.

SELECT substring(filename,1,1) as fn FROM Table_Name WHERE filename LIKE 'a%';

if ($row['fn'] == "a")
do something
else if ($row['fn'] == "b")
do something else

JAG

[seomike2003]

Just remember when you use LIKE it can be slow.

[Timotheos]

Hmmm... so would
SELECT LEFT(filename, 1) as fn FROM Table_Name WHERE fn='a';
be faster?

[dreamcatcher]

I`ve never noticed too much difference when using LIKE before to be honest. Anyway, thanks guys, your combined ideas are exactly what I`ve been looking for.

:)

[andrew_m]

SELECT ... name LIKE 'a%'
will be fast if there is an index on 'name'. If there is no index or if the condition is more like '%a%' it will have to scan each entry in the database to come up with the resulting set.