case-insensitive in 'like' sql statement

enterume

How do I do a sql statement which is in case-sensitive in the 'like' sql statement?

For example, below is my sql statement, which I wish to perform in case sensitive in the search condition:

===================================

select * from TableSearch where user_id='325' AND header_subject like '%NOki%' order by header_date desc

I know that 'like' in sql statement will force the search string to be case-insensitive. Any solution for this?

Thanks in advance.

enterume

I found that there is some solution for this by adding the 'BINARY' in the field's keyword. But what I worry here is , if the user wish to serarch from my db in case-insensitive, how?

Currently i wish to let the user to serach thru my db either case-sensitive or case-insensitive. Any solution for this will be appreciated.

Thanks.

enterume wrote:

How do I do a sql statement which is in case-sensitive in the 'like' sql statement?

For example, below is my sql statement, which I wish to perform in case sensitive in the search condition:

===================================

select * from TableSearch where user_id='325' AND header_subject like '%NOki%' order by header_date desc

I know that 'like' in sql statement will force the search string to be case-insensitive. Any solution for this?

Thanks in advance.

Anon

if there are two arguments you have to split them up to do the right thing

select *
from TableSearch
where (user_id='325')
AND (header_subject like '%NOki%')
order by header_date desc

Anon

thanks.

But my question here is about the case-sensitive and case-insensitive sql query.. any advice?

shanx24

I think the only way to get this done is

select * from TableSearch
where user_id='325'
AND UPPER(header_subject) like UPPER('%NOki%')
order by header_date desc

Two observations:

If this slows your query down a lot, you might like to create an index on UPPER(header_subject).
You could also try the following alternative:

select * from TableSearch
where user_id='325'
AND INSTR(UPPER(header_subject), UPPER('%NOki%'))
order by header_date desc

Hope this helps
Shanx

shanx24

Correction:

Sorry I hope you got the general drift, but here is more technically correct solution:

select * from TableSearch
where user_id='325'
AND INSTR(UPPER(header_subject), UPPER('NOki'))
order by header_date desc

enterume

I still got the same result, as MySQL will return me any string which contain 'noki', instead of only return 'NOKi' data field.

The purpose of UPPER is use to convert the select string to UPPERCASE ??

Shashank Tripathi wrote:

Correction:

Sorry I hope you got the general drift, but here is more technically correct solution:

select * from TableSearch
where user_id='325'
AND INSTR(UPPER(header_subject), UPPER('NOki'))
order by header_date desc