MySQL search problem to find lowercase and uppercase letters at the sametime

Question

I have a table where users enters there username in both lower case(eg: arup) and uppercase(eg: Arup) or both (eg: aRuP).

But my problem is now that if I search database to show member username like %Arup, mysql returns empty result if not founds exactly.

 id    |   username   |   name    |   sex
  1         arUp         Arup Sarma   Male

<?php
$q=strip_tags($_POST['user']); /// eg. Arup
$qry=mysql_fetch_row(mysql_query("SELECT id,name,sex FROM membertable WHERE username='%$q'"));
echo $qry['0']."<br>".$qry['1']."<br>".$qry['2'];
?>
/// Now above sql query will return zero result as there is no username in the form of arUp.

How to make SQL query Case-insensitive ? anyone help please...

while not a duplicate, this question provides useful related answers https://stackoverflow.com/questions/16558967/how-to-check-for-uppercase-letters-in-mysql — Kay V, Oct 04 '22 at 19:01

score 6 · Accepted Answer · answered Sep 24 '11 at 16:52

6

I think that this link could help to understand your problem...
Anyway you could solve your problem with this:

SELECT * FROM your_table 
WHERE LOWER(username) LIKE '%arup'

answered Sep 24 '11 at 16:52

Marco

56,740
14
129
152

score 2 · Answer 2 · answered Sep 24 '11 at 16:56

You can call UPPER on both sides of the comparison:

SELECT id,name,sex FROM membertable WHERE UPPER(username) = UPPER('%$q')

The "right way", according to the official manual, is choosing a case-insensitive collation, which might be faster but is also more complicated.

score 0 · Answer 3 · answered Sep 24 '11 at 16:55

0

based on this: http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

i'd try this:

where username COLLATE latin1_swedish_ci = '%$q'

answered Sep 24 '11 at 16:55

Ramy

20,541
41
103
153

MySQL search problem to find lowercase and uppercase letters at the sametime

3 Answers3