Help with query

I want to search the database for users who have email addresses whose email
username matches their stored username.

So assuming my table is like so:

TABLE `users`
id | username | email
1 | jim | user@domain.com
2 | bob | bob@domain.com
3 | jen | jen13@domain.com

So I want to search like "SELECT * from `users` where
email=username@userdomain.com", which in the case above would return row #2
only, as user bob has email bob@domain.com, and not row 1 as email username
is nowhere near the username, and not row 3 as this username does not match
100%.

I know I will need to use regex and all that, I'm just not sure about how to
correctly set up the query itself to get the correct records.

Thanks for any help you can give!

Raven

Re: Help with query

_Raven wrote:
> I want to search the database for users who have email addresses whose email
> username matches their stored username.
>
> So assuming my table is like so:
>
> TABLE `users`
> id | username | email
> 1 | jim | user@domain.com
> 2 | bob | bob@domain.com
> 3 | jen | jen13@domain.com
>
> So I want to search like "SELECT * from `users` where
> email=username@userdomain.com", which in the case above would return row #2
> only, as user bob has email bob@domain.com, and not row 1 as email username
> is nowhere near the username, and not row 3 as this username does not match
> 100%.
>
> I know I will need to use regex and all that, I'm just not sure about how to
> correctly set up the query itself to get the correct records.

You could try with

SELECT email FROM users WHERE username=SUBSTR(eMail,1,INSTR(eMail,'@')-1);


//Aho

Re: Help with query

That works perfectly! It was the INSTR that was throwing me off.

Thanks Aho!

"J.O. Aho" wrote in message
news:4ndbnsF9u8kgU1@individual.net...
> _Raven wrote:
>> I want to search the database for users who have email addresses whose
>> email username matches their stored username.
>>
>> So assuming my table is like so:
>>
>> TABLE `users`
>> id | username | email
>> 1 | jim | user@domain.com
>> 2 | bob | bob@domain.com
>> 3 | jen | jen13@domain.com
>>
>> So I want to search like "SELECT * from `users` where
>> email=username@userdomain.com", which in the case above would return row
>> #2 only, as user bob has email bob@domain.com, and not row 1 as email
>> username is nowhere near the username, and not row 3 as this username
>> does not match 100%.
>>
>> I know I will need to use regex and all that, I'm just not sure about how
>> to correctly set up the query itself to get the correct records.
>
> You could try with
>
> SELECT email FROM users WHERE username=SUBSTR(eMail,1,INSTR(eMail,'@')-1);
>
>
> //Aho

Re: Help with query

_Raven wrote:
> I want to search the database for users who have email addresses whose email
> username matches their stored username.
>
> So assuming my table is like so:
>
> TABLE `users`
> id | username | email
> 1 | jim | user@domain.com
> 2 | bob | bob@domain.com
> 3 | jen | jen13@domain.com
>
> So I want to search like "SELECT * from `users` where
> email=username@userdomain.com", which in the case above would return row #2
> only, as user bob has email bob@domain.com, and not row 1 as email username
> is nowhere near the username, and not row 3 as this username does not match
> 100%.
>
> I know I will need to use regex and all that, I'm just not sure about how to
> correctly set up the query itself to get the correct records.
>
> Thanks for any help you can give!
>
> Raven
>
>

Your SQL statement that you need would be (no regex stuff here):

SELECT username,email FROM table WHERE
(username = SUBSTR(email,1,LENGTH(username)))
AND
LENGTH(username) = (LOCATE('@',email ) - 1);

--
Thanks in Advance...
IchBin, Pocono Lake, Pa, USA http://weconsultants.phpnet.us
____________________________________________________________ ______________

'If there is one, Knowledge is the "Fountain of Youth"'
-William E. Taylor, Regular Guy (1952-)