SQL- Ignore case while searching for a string
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%')
SQL SELECT LIKE (Insensitive casing)
use LOWER Function in both (column and search word(s)). Doing it so, you assure that the even if in the query is something like %VaLuE%, it wont matter
select qt.*
from query_table qt
where LOWER(column_name) LIKE LOWER('%vAlUe%');
SQL server ignore case in a where expression
In the default configuration of a SQL Server database, string comparisons are case-insensitive. If your database overrides this setting (through the use of an alternate collation), then you'll need to specify what sort of collation to use in your query.
SELECT * FROM myTable WHERE myField = 'sOmeVal' COLLATE SQL_Latin1_General_CP1_CI_AS
Note that the collation I provided is just an example (though it will more than likely function just fine for you). A more thorough outline of SQL Server collations can be found here.
How can I search (case-insensitive) in a column using LIKE wildcard?
SELECT *
FROM trees
WHERE trees.`title` COLLATE UTF8_GENERAL_CI LIKE '%elm%'
Actually, if you add COLLATE UTF8_GENERAL_CI
to your column's definition, you can just omit all these tricks: it will work automatically.
ALTER TABLE trees
MODIFY COLUMN title VARCHAR(…) CHARACTER
SET UTF8 COLLATE UTF8_GENERAL_CI.
This will also rebuild any indexes on this column so that they could be used for the queries without leading '%'
SQL Case insensitive IN search
The easiest way would be:
SELECT Serial
FROM Table
WHERE upper(Serial) in ('D100M', 'D200M');
That won't however use an index on the serial
column.
So if performance is a concern you would need to create an index on upper(serial)
.
Ignore case sensitive for given word
Please try lower
in all cells you reference as well as all depending, returning columns.
So your formula would be:
="select B, C, L, E, O, P, Q WHERE B >= date '"&TEXT(A2; "yyy-mm-dd")&"' and B <= date '"&TEXT(B2; "yyy-mm-dd")&"' and lower(L) matches '.*"&lower(C2)&".*' and lower(E) LIKE '%"&lower(D2)&"%'"
Perform a Case insensitive Like query in a case sensitive SQL Server database
You can use UPPER
or LOWER
functions to convert the values to the same case. For example:
SELECT *
FROM YourTable
WHERE UPPER(YourColumn) = UPPER('VALUE')
Alternatively, you can specify the collation manually when comparing:
SELECT *
FROM YourTable
WHERE YourColumn = 'VALUE' COLLATE SQL_Latin1_General_CP1_CI_AI
Case insensitive searching in Oracle
Since 10gR2, Oracle allows to fine-tune the behaviour of string comparisons by setting the NLS_COMP
and NLS_SORT
session parameters:
SQL> SET HEADING OFF
SQL> SELECT *
2 FROM NLS_SESSION_PARAMETERS
3 WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');
NLS_SORT
BINARY
NLS_COMP
BINARY
SQL>
SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH
2 FROM DUAL;
0
SQL>
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;
Session altered.
SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;
Session altered.
SQL>
SQL> SELECT *
2 FROM NLS_SESSION_PARAMETERS
3 WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');
NLS_SORT
BINARY_CI
NLS_COMP
LINGUISTIC
SQL>
SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH
2 FROM DUAL;
1
You can also create case insensitive indexes:
create index
nlsci1_gen_person
on
MY_PERSON
(NLSSORT
(PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')
)
;
This information was taken from Oracle case insensitive searches. The article mentions REGEXP_LIKE
but it seems to work with good old =
as well.
In versions older than 10gR2 it can't really be done and the usual approach, if you don't need accent-insensitive search, is to just UPPER()
both the column and the search expression.
Related Topics
How to Get a Hash of an Entire Table in Postgresql
How to Do a Find/Replace in T-Sql
How to Remove Not Null Constraint in SQL Server Using Query
How to Emulate Tagged Union in a Database
Fastest "Get Duplicates" SQL Script
Postgresql Query for Getting N-Level Parent-Child Relation Stored in a Single Table
Insert Default Value When Parameter Is Null
SQL Query Selecting Different Row Result in JSON_Modify Because of in Operator Provided Value
Tsql Left Join and Only Last Row from Right
How to Clear All Cached Items in Oracle
How to Check If Value Exists in Each Group (After Group By)
Finding the Hash Value of a Row in Postgresql
How to Constraint No Empty Strings on an Nvarchar Column