On Tue, 2004-05-11 at 10:45, Lionel Pitaru wrote:
> Hello
> I would like to make a question: I'm creating a list with a scripting lenguage with
> MySql database. The list presents username, first name and last name of persons. It
> has three pairs of filters for username, first name and last name. For each of them
> the user of the list can type a text as limit for his field with a from and until
> manner. I would like to create this query with regexp, but i can't make it work.
> An example of working in this:
>
> Username First name Last name
> -------------------------------------------------------------
> alberto alberto gomez
> ariel ariel ramirez
> leandro lean123 pass123
>
> with this filters:
> username from 'a' until 'le'
> first name from 'al' until 'lean'
> there should be the hole list
>
> but with this filters:
> username from 'a' until 'la'
> first name from 'al' until 'lean'
> there should be this list:
>
> Username First name Last name
> -------------------------------------------------------------
> alberto alberto gomez
> ariel ariel ramirez
>
> I hope i was clear and you could help me with this.
You can use 'between' to do this. The only catch is that your ranges
don't completely cover the dataset you want returned in the traditional
manner. As an example, if we were using numbers, what you are asking
for is something like asking for numbers between 2 and 7 from the list:
1
2.3
4
6
7.8
9
and desiring the result to be
2.3
4
6
7.8
In this case asking for all numbers 2 <= X <= 7 doesn't work because you
want ANY number starting with a 7. So, to get the affect that you want,
you'd have to ask for 2 <= X <= (7+1).
Likewise, for your first example you'd use:
SELECT * FROM names WHERE username BETWEEN 'a' and 'ld' AND
first_name BETWEEN 'al' and 'leao'
Note that for the end condition of each comparison the last letter is
increased by one letter so that it completely covers all words that
begin with the target letters.
The second example would be:
SELECT * FROM names WHERE username BETWEEN 'a' and 'lb' AND
first_name BETWEEN 'al' and 'leao'
HTH
--
. Garth Webb
. [EMAIL PROTECTED]
.
. shoes * éå * schoenen * ëí * chaussures * zapatos
. Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑÐÐÐÐ
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
