Search exact text in datareader

Gertrudis Carreras

Hi to all,

I have a datareader that return the list of all users in a database, but always return text in lower or uppercase:

string CommandText = "SELECT username, password " + " FROM users" + " WHERE username = @Find";

users in database:

jose

maria

carlos

if Iwrite: JOSE in upper case, it shows the one in lowercase how to avoid this please.

Thanks in Advance

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sanjeeb Lenka13y ago
Accepted answer
Try this query:

string CommandText = "SELECT UPPER(username) as Username, password " + " FROM users" + " WHERE username = @Find";

or if you want case sensitive select then use this query

string CommandText = "SELECT UPPER(username) as Username, password " + " FROM users" + " WHERE username COLLATE Latin1_General_CS_AS = @Find";

you can follow this link also

http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/

Any problem reply
Jignesh Trivedi13y ago
hi,

yes agree with Pankaj, if you want to made compairison in C# then you may use this approch else you can also use equal method of string...
like...

string d = "SomeText";
if (d.Equals("othertext", StringComparison.Ordinal))
{
// Do some thing
}

hope this will help you.
Pankaj Pandey13y ago
hiii

if you want to search it in front eend then try this logic

string str="jose";//or any value in either lower or Upper

if(dr[i]["column"].ToString().ToUpper()==str.ToUpper())
{
//your logic here.
}
Jignesh Trivedi13y ago
hi,
you want to case Sensitive search with SQL, right?
In this case you can use COLLATE with where clause
try..
SELECT username, password FROM users WHERE username = 'test' COLLATE SQL_Latin1_General_CP1_CI_AS
hope this will help you.
chetan tembhre13y ago
Try below query
SELECT * FROM Users WHERE Name ='Chetan=' COLLATE SQL_Latin1_General_CP1_CS_AS