[SOLVED] MYSQL help......

[thenature4u]

I have a table tbluserdetails.

 

username  Joindate              email

kElvin        2008-10-9            abc@xyz.com

 

alex          2008-10-9            abcf@xyz.com

 

Austin      2008-10-9            abce@xyz.com

 

john          2008-10-9            abcd@xyz.com

 

With mysql query, i want to retrieve the user records, who has capital letter in their username.

 

My required output is:

 

username  Joindate              email

kElvin        2008-10-9            abc@xyz.com

Austin      2008-10-9            abce@xyz.com

 

Help me...

 

Thanks in advance.....

 

[PFMaBiSmAd]

The easiest way would be to use the mysql LOWER() function to convert the value to lower case, use the mysql BINARY operator to force the result to a binary string so that comparisons are case-sensitive, and then use the mysql STRCMP() function to check if the result is the same as to original value. If it is the same, there were no upper case letters in the value.

[thenature4u]

Thanks for your response. I am new to mysql.

 

Can you explain me in a clear way. If you done mind, can you tell the query for this.

 

Thanks in advance.

[PFMaBiSmAd]

Your first step would be to research the LOWER(), BINARY, and STRCMP() functions in the mysql manual to see what they do and how to use them.

[thenature4u]

thanks for your response,

 

SELECT *

FROM tablename

WHERE HEX( LOWER( username ) ) = HEX( username )

 

 

By using this query i am able to retrieve user  records who are not having capital letters in their username.

 

how to make not eqval to for the above query. So that i can get required output.

[PFMaBiSmAd]

If using = in the comparison finds the ones already in lower-case, then using <> in the comparison should find the ones with upper-case.

[thenature4u]

Thanks for your help.

 

I got it. This is the query for that one.

 

SELECT *

FROM tbluserdetails

WHERE STRCMP( HEX( LOWER( username ) ) , HEX( username ) ) =1

 

 

Thanks once again.

 

 

[fenway]

Why are you comparing the HEX?

[PFMaBiSmAd]

Because

 

STRCMP(LOWER(username) , username) =1

 

would be case insensitive and would equal 0 when username contained any upper case letters.

 

I had suggested using the BINARY operator to force the comparison to be case sensitive, but HEX() gives the same results.

[fenway]

Of course...