Need help for SQL query

Question

0.00/5 (No votes)

See more:

How to write sql query to retrieve user_id,user_name and password of the given user??

I have written the query

SQL

SELECT USER_ID,USER_NAME,PASSWORD FROM NC_USER WHERE USER_NAME = @USERNAME;

here USERNAME will be give by the end user.

In my mysql database the user table is saved in lower case.
Example:
user_id user_name password
1 chinmoyp m
2 sumitd mi
3 radhamadhaba min
4 bhagirathip mind
5 rashmis mindf

when i enter 'CHinmOYp' it is showing result.
user_id user_name password
1 chinmoyp m

But actually it should say invalid user name(error).

How to write a query so that the if we enter 'ChinmOYp' it will show an error message that the user name is not present.

Please Suggest

Thanks

Posted 5-Apr-12 23:32pm

bhagirathimfs

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Loke.mysore · Accepted Answer · 2012-04-06T02:27:00

Solution 2

You can try this..

SQL

SELECT USER_ID,USER_NAME,PASSWORD FROM NC_USER WHERE USER_NAME LIKE BINARY @USERNAME;

this binary keyword compares binary values so u can solve your problem

Posted 6-Apr-12 2:27am

Loke.mysore

Comments

bhagirathimfs 6-Apr-12 9:41am

Thanks :)

OriginalGriff · Accepted Answer · 2012-04-05T23:43:00

Solution 1

You can do it by forcing the COLLATION to case sensitive:

SQL

SELECT USER_ID,USER_NAME,PASSWORD FROM NC_USER WHERE USER_NAME = @USERNAME COLLATE SQL_Latin1_General_CP1_CS_AS

Posted 5-Apr-12 23:43pm

OriginalGriff

Comments

bhagirathimfs 6-Apr-12 5:46am

It is showing error
"unknown collation:'SQL_Latin1_General_CP1_CS_AS'"

bhagirathimfs 6-Apr-12 5:47am

SELECT USER_ID,USER_NAME,PASSWORD FROM NC_USER WHERE USER_NAME = ''CHinmoYP' COLLATE SQL_Latin1_General_CP1_CS_AS
when i execute this query it is showing the above error.

OriginalGriff 6-Apr-12 5:56am

It may not be in your local SQL installation.
Run this query:
SELECT * FROM fn_helpcollations()
It will list all the collations installed. Chose an appropriate one from the list: the Description field needs to include "case-sensitive"

bhagirathimfs 6-Apr-12 7:02am

The select query is showing error.
ERROR:U have an errorin the sql syntax;check manual that corresponds to your MySql server for the right syntax to use () in line 1.

Actually i am using the MYSQL 5.2

Can u suggest another way to know the collations.

OriginalGriff 6-Apr-12 7:05am

Ah! MySql.
Have a look here: http://dev.mysql.com/doc/refman/5.5/en/charset-charsets.html

bhagirathimfs 6-Apr-12 8:05am

Wow this is a nice website.
Thanks

But can you please tell the collation that i need.I didn't get that one from there. :(

OriginalGriff 6-Apr-12 8:18am

Have you tried latin1_general_cs?

bhagirathimfs 6-Apr-12 8:36am

It is showing an Error.
Error:
1253Collation 'latin1_genetal_cs' is not valid for character set 'utf8'

OriginalGriff 6-Apr-12 8:47am

They aren't making it easy here, are they? :sigh:
try:
show collation where charset = 'utf8'
That should list the valid ones - try to find one with "cs" in it.
Or possibly:
SELECT USER_ID,USER_NAME,PASSWORD FROM NC_USER WHERE CONVERT(USER_NAME USING latin1) COLLATE latin1_general_cs = @Username

where convert(pageTitle using latin1) collate latin1_general_cs = 'Something'

bhagirathimfs 6-Apr-12 9:32am

Thanks OriginalGriff

Thanks 4 giving your valuable time for my problem

Thanks a ton.

OriginalGriff 6-Apr-12 9:57am

:laugh:
You're welcome!