Jump to content

[SOLVED] Return results beginning with first initial


Recommended Posts

Hi,

 

I need to write a query to return all results beginning with a certain initial character and this character should be alphabetically (or numerically) the first in all results (...not very clear, soz). For example, I have a database with the following rows:

 

banana, bonzai, butter, cat, cough, dog, doughnut

 

I would want the query to return 'banana', 'bonzai', 'butter' ('b' is the first initial in the database).

 

As a starter, so far I have got:

 

SELECT word FROM tablename WHERE...

 

Impressed? ;)

@revraz: thanks for your help, it got me thinking and I then came up with the following:

 

SELECT word FROM tablename WHERE LEFT(LOWER(word),1) = (SELECT LEFT(LOWER(word),1) FROM tablename ORDER BY word LIMIT 1)

 

which works nicely. I'm pretty confident that there must be a more efficient query than this though, so if anyone fancies a go...

 

Ta

You can use WHERE fieldname LIKE b%  if you just want what starts with b, or you can replace b with a variable.

 

The problem is I don't know what the first initial ('b' in this case) is going to be. The subquery

SELECT LEFT(LOWER(word),1) FROM tablename ORDER BY word LIMIT 1

works that out but I could not figure out a way of using a LIKE comparison in the main query:

 

SELECT word FROM tablename WHERE LOWER(word) LIKE (SELECT LEFT(LOWER(word),1) FROM tablename ORDER BY word LIMIT 1)

 

This then doesn't let me use the '%' extender.

Why LOWER()? That's bad for index usage!

The lower is just in place to ensure the correct case. The field `word` is not indexed. Could you explain why you say it is bad for index usage - I must be misunderstanding something ???
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.