[SOLVED] MYSQL help......

[thenature4u]

I have a table tbluserdetails.

 

username  Joindate              email

kElvin        2008-10-9            [email protected]

 

alex          2008-10-9            [email protected]

 

Austin      2008-10-9            [email protected]

 

john          2008-10-9            [email protected]

 

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            [email protected]

Austin      2008-10-9            [email protected]

 

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...