Menu
Case insensitive SQL SELECT: Use upper or lower functions 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.
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. The logic of this query is perfectly reasonable but the execution plan is not: DB2.
SQL syntax is generally case insensitive. If LIKE and like lead to different results that means that your SQL syntax is case sensitive, which I've never seen before. The documentation for Hibernate says " sELEct is the same as SELECT ".
To do a case-insensitive comparison, use the ILIKE keyword; e.g., column ILIKE 'aBc' and column ILIKE 'ABC' both return TRUE for 'abc' . In contrast, MySQL and MS SQL Server have case-insensitive behaviors by default. This means WHERE column = 'abc' returns TRUE for e.g., 'abc' , 'ABC' , or 'aBc' .
Use something like this -
SELECT DISTINCT COL_NAME FROM myTable WHERE UPPER(COL_NAME) LIKE UPPER('%PriceOrder%')
or
SELECT DISTINCT COL_NAME FROM myTable WHERE LOWER(COL_NAME) LIKE LOWER('%PriceOrder%')
Like this.
SELECT DISTINCT COL_NAME FROM myTable WHERE COL_NAME iLIKE '%Priceorder%'
In postgresql.
See this similar question and answer to searching with case insensitivity - SQL server ignore case in a where expression
Try using something like:
SELECT DISTINCT COL_NAME
FROM myTable
WHERE COL_NAME COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%priceorder%'
You should probably use SQL_Latin1_General_Cp1_CI_AS_KI_WI as your collation. The one you specify in your question is explictly case sensitive.
You can see a list of collations here.
If someone using ORACLE can be changes at database level itself
ALTER SESSION SET NLS_COMP=LINGUISTIC;
ALTER SESSION SET NLS_SORT=BINARY_CI;
Note:- Oracle
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