Alphabetical search to and from

MySQL,

This should be a fairly simple question.

I have a table with a bunch of people's names. I want to find people
who's name begins within a certain range of characters.

All names between F and P, for example.

What SELECT statement would I use to do that?

Thank you for any advice.

--
Dave M G

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Alphabetical search to and from

Dave M G wrote:
> MySQL,
>
> This should be a fairly simple question.
>
> I have a table with a bunch of people's names. I want to find people
> who's name begins within a certain range of characters.
>
> All names between F and P, for example.
>
> What SELECT statement would I use to do that?
>
> Thank you for any advice.
>
>
Slow version (no use of index) :
select username from users where left(username,1) between "A" and "B";

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Alphabetical search to and from

Try this:
select * from country where LEFT(Name, 1) between 'F' and 'P';

Regards,
m.

-----Original Message-----
From: Dave M G [mailto:dave@articlass.org]
Sent: 04 November 2009 14:52
To: mysql@lists.mysql.com
Subject: Alphabetical search to and from

MySQL,

This should be a fairly simple question.

I have a table with a bunch of people's names. I want to find people
who's name begins within a certain range of characters.

All names between F and P, for example.

What SELECT statement would I use to do that?

Thank you for any advice.

--
Dave M G

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=misiaq@poczta.fm



------------------------------------------------------------ ----------
Konkurs: Wygrywaj nagrody z Pudzianem!
Sprawd¼ >> http://link.interia.pl/f240e


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Alphabetical search to and from

Haven't tried this myself, but would this not work while permitting
the index to engage?

select * from country Name between 'FAAAAAAAAAA' and 'PZZZZZZZZZZ'

On Wed, Nov 4, 2009 at 10:15 AM, misiaQ wrote:
> Try this:
> select * from country where LEFT(Name, 1) between 'F' and 'P';
>
> Regards,
> m.
>
> -----Original Message-----
> From: Dave M G [mailto:dave@articlass.org]
> Sent: 04 November 2009 14:52
> To: mysql@lists.mysql.com
> Subject: Alphabetical search to and from
>
> MySQL,
>
> This should be a fairly simple question.
>
> I have a table with a bunch of people's names. I want to find people
> who's name begins within a certain range of characters.
>
> All names between F and P, for example.
>
> What SELECT statement would I use to do that?
>
> Thank you for any advice.
>
> --
> Dave M G
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmisiaq@poczta=
..fm
>
>
>
> ------------------------------------------------------------ ----------
> Konkurs: Wygrywaj nagrody z Pudzianem!
> Sprawd=BC >> http://link.interia.pl/f240e
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
- michael dykman
- mdykman@gmail.com

"May you live every day of your life."
Jonathan Swift

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Alphabetical search to and from

Hi,

You're right on one hand. But on the another in some languages you have =
a
more characters after the Z letter.
Apart from that try this:

select 'F' as test
union
select 'FAA'
order by test;

and you'll see:

test
---------
F
FAA

Regards,
m.

-----Original Message-----
From: Michael Dykman [mailto:mdykman@gmail.com]=20
Sent: 04 November 2009 15:19
To: Dave M G
Cc: mysql@lists.mysql.com
Subject: Re: Alphabetical search to and from

Haven't tried this myself, but would this not work while permitting
the index to engage?

select * from country Name between 'FAAAAAAAAAA' and 'PZZZZZZZZZZ'

On Wed, Nov 4, 2009 at 10:15 AM, misiaQ wrote:
> Try this:
> select * from country where LEFT(Name, 1) between 'F' and 'P';
>
> Regards,
> m.
>
> -----Original Message-----
> From: Dave M G [mailto:dave@articlass.org]
> Sent: 04 November 2009 14:52
> To: mysql@lists.mysql.com
> Subject: Alphabetical search to and from
>
> MySQL,
>
> This should be a fairly simple question.
>
> I have a table with a bunch of people's names. I want to find people
> who's name begins within a certain range of characters.
>
> All names between F and P, for example.
>
> What SELECT statement would I use to do that?
>
> Thank you for any advice.
>
> --
> Dave M G
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =
=A0http://lists.mysql.com/mysql?unsub=3Dmisiaq@poczta.fm
>
>
>
> ------------------------------------------------------------ ----------
> Konkurs: Wygrywaj nagrody z Pudzianem!
> Sprawd=BC >> http://link.interia.pl/f240e
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =
=A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail.com
>
>



--=20
- michael dykman
- mdykman@gmail.com

"May you live every day of your life."
Jonathan Swift

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmisiaq@poczta.fm



Tanie rozmowy telefoniczne!
Sprawdz >> http://link.interia.pl/f2410


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Alphabetical search to and from

Good point about that first position. Of course, the ending would
need an interpretation according to the charset at hand.

select * from country Name between 'F' and 'P\uffff\uffff\uffff\uffff\ufff=
f'

- md

On Wed, Nov 4, 2009 at 10:26 AM, misiaQ wrote:
> Hi,
>
> You're right on one hand. But on the another in some languages you have a
> more characters after the Z letter.
> Apart from that try this:
>
> select 'F' as test
> union
> select 'FAA'
> order by test;
>
> and you'll see:
>
> test
> ---------
> F
> FAA
>
> Regards,
> m.
>
> -----Original Message-----
> From: Michael Dykman [mailto:mdykman@gmail.com]
> Sent: 04 November 2009 15:19
> To: Dave M G
> Cc: mysql@lists.mysql.com
> Subject: Re: Alphabetical search to and from
>
> Haven't tried this myself, but would this not work while permitting
> the index to engage?
>
> select * from country Name between 'FAAAAAAAAAA' and 'PZZZZZZZZZZ'
>
> On Wed, Nov 4, 2009 at 10:15 AM, misiaQ wrote:
>> Try this:
>> select * from country where LEFT(Name, 1) between 'F' and 'P';
>>
>> Regards,
>> m.
>>
>> -----Original Message-----
>> From: Dave M G [mailto:dave@articlass.org]
>> Sent: 04 November 2009 14:52
>> To: mysql@lists.mysql.com
>> Subject: Alphabetical search to and from
>>
>> MySQL,
>>
>> This should be a fairly simple question.
>>
>> I have a table with a bunch of people's names. I want to find people
>> who's name begins within a certain range of characters.
>>
>> All names between F and P, for example.
>>
>> What SELECT statement would I use to do that?
>>
>> Thank you for any advice.
>>
>> --
>> Dave M G
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmisiaq@poczt=
a.fm
>>
>>
>>
>> ------------------------------------------------------------ ----------
>> Konkurs: Wygrywaj nagrody z Pudzianem!
>> Sprawd=BC >> http://link.interia.pl/f240e
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmai=
l.com
>>
>>
>
>
>
> --
> =A0- michael dykman
> =A0- mdykman@gmail.com
>
> "May you live every day of your life."
> =A0 =A0Jonathan Swift
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmisiaq@poczta=
..fm
>
>
>
> Tanie rozmowy telefoniczne!
> Sprawdz >> http://link.interia.pl/f2410
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
- michael dykman
- mdykman@gmail.com

"May you live every day of your life."
Jonathan Swift

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Alphabetical search to and from

Or :
alter table users add first_f_name char(1) not null;
create index first_f_name_idx on users (first_f_name);
update users set first_f_name = left(first_name,1);

And not the query will use index.
select username from users where first_f_name between "A" and "B";

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Alphabetical search to and from

> Or :
> alter table users add first_f_name char(1) not null;
> create index first_f_name_idx on users (first_f_name);
> update users set first_f_name = left(first_name,1);

.... and don't forget the trigger on insert, update to keep that new column
always up to date.

Regards,
m.


------------------------------------------------------------ ----------
Konkurs: Wygrywaj nagrody z Pudzianem!
Sprawd¼ >> http://link.interia.pl/f240e


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Alphabetical search to and from

At 11:52 PM +0900 11/4/09, Dave M G wrote:
>MySQL,
>
>This should be a fairly simple question.
>
>I have a table with a bunch of people's names. I want to find people
>who's name begins within a certain range of characters.
>
>All names between F and P, for example.
>
>What SELECT statement would I use to do that?
>
>Thank you for any advice.
>
>--
>Dave M G


And to add a few more possibilities:

select * from your_table where name >= 'F' and name < 'Q'

select * from your_table where left(name, 1) in
('f','g','h','i','j','k','l','m','n','o','p')

I wouldn't recommend the latter, but might be handy if you were
dealing with something more complex than a simple range.

- steve

--
+----------------------------------------------------------- -------------+
| Steve Edberg edberg@edberg-online.com |
| Programming/Database/SysAdmin http://www.edberg-online.com/ |
+----------------------------------------------------------- -------------+

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Alphabetical search to and from [SOLVED]

Thanks to everyone for their help.

A lot of the suggested code unfortunately did not seem to work for me.

In the end I found it was easier to use UNION and select each
alphabetical letter I wanted to include.

It's not elegant, but it works perfectly.

Thanks again for all the help.

--
Dave M G

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org