My sql Query to filter "FREE" and "free" in a Table

Question

0.00/5 (No votes)

See more:

Hi All

I have a Table in which there is a column "Usertype".
In usertype column two values are there ("FREE","free").

I want to separate those records based on usertype.

like
(1) "select from userinfo where usertype='FREE'"
(2) "select from userinfo where usertype='free'"

But in both case all the records are coming.

Can anybody please help to find out what exact query should be to separate those records.

I will be thankful if I will get the solution.

Regards
Asutosha Sarangi

Posted 13-Sep-11 20:26pm

Asutosha

Add a Solution

4 solutions

Solution 2

You need to use COLLATE
http://mysqlpreacher.com/wordpress/2009/08/running-a-case-sensitive-query-in-on-a-case-insensitive-table/[^]

Posted 13-Sep-11 20:33pm

Prerak Patel

Updated 13-Sep-11 20:35pm

v2

Comments

Thiagarajan Duraisamy 14-Sep-11 2:39am

he is asking for the query na,
some time the query can be used to generate bost results in that case using collate will not help na.
its a suggestion tat, using binary check sum is sufficient for query, than modifing the table.

Prerak Patel 14-Sep-11 2:53am

It's not about changing table at all.
Link show that COLLATE can be used in query too.
select * from t1 where CONVERT(a USING latin1) COLLATE latin1_general_cs ='Darren'

Solution 3

SQL

select * from <table name=""> where (binary_checksum(Usertype) = binary_checksum('free'))</table>

hope this helps.

Posted 13-Sep-11 20:34pm

Thiagarajan Duraisamy

Updated 13-Sep-11 20:35pm

v2

Solution 4

Another option:
~~LIKE is case sensitive so you can use~~

According to
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like[^] LIKE is case sensitive when one of the operands is a binary string.

SQL

select from userinfo where usertype LIKE BINARY 'FREE'

SQL

select from userinfo where usertype LIKE BINARY 'free'

Posted 13-Sep-11 20:36pm

Simon Bang Terkildsen

Updated 13-Sep-11 20:54pm

v2

Comments

Simon Bang Terkildsen 14-Sep-11 2:54am

very true, I read the documentation wrong, I've updated the answer.

Thiagarajan Duraisamy 14-Sep-11 23:51pm

ya now its fine

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)

Mehdi Gholam · Accepted Answer · 2011-09-13T20:31:00

Solution 1

You have to enable case sensitivity for your column like this on SQL Server:

SQL

ALTER TABLE userinfo
    ALTER COLUMN UserType VARCHAR(10) 
    COLLATE SQL_Latin1_General_CP1_CS_AS

Posted 13-Sep-11 20:31pm

Mehdi Gholam

Updated 13-Sep-11 20:35pm

v2

Comments

Thiagarajan Duraisamy 14-Sep-11 2:39am

he is asking for the query na,
some time the query can be used to generate bost results in that case using collate will not help na.
its a suggestion tat, using binary check sum is sufficient for query, than modifing the table.

Simon Bang Terkildsen 14-Sep-11 2:44am

Mehdi is posting a good option to the OP, might not be exactly what the OP is after, but it is an option. Just like your suggestion which might actually be less usefull as I don't believe MySQL has binary_checksum

Thiagarajan Duraisamy 14-Sep-11 2:53am

thanks yar.

Thiagarajan Duraisamy 14-Sep-11 2:59am

but u can use 'binary' to check tat na