Menu
i need to query my database and find results :
mysql_query("select * from ".ALU_TABLE." where username like '%$q%' or name like '%$q%'");
if i have a name in my table such as Book and i enter book in search box it wont show the Book
i need to query my database as not to be case sensitive.
839
The syntax is as follows: SELECT DISTINCT UPPER(yourColumnName) FROM yourTableName; Case 2: Using LOWER(). Here is the query to select case-insensitive distinct.
Another way for case-insensitive matching is to use a different “collation”. The default collations used by SQL Server and MySQL do not distinguish between upper and lower case letters—they are case-insensitive by default.
Table names are stored in lowercase on disk and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
Case insensitive SQL SELECT: Use upper or lower functions or this: select * from users where lower(first_name) = 'fred'; As you can see, the pattern is to make the field you're searching into uppercase or lowercase, and then make your search string also be uppercase or lowercase to match the SQL function you've used.
You can use the LOWER() function
... WHERE LOWER(username) LIKE blabl OR LOWER(name) LIKE asdasd
You need to append something like COLLATE utf8_general_ci to your query. (The _ci suffix stands for case insensitive.)
Have a look at the documentation: 9.1.7.1. Using COLLATE in SQL Statements:
With the COLLATE clause, you can override whatever the default collation is for a comparison. COLLATE may be used in various parts of SQL statements.
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
