SQL Server Ignore Case in a Where Expression

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.

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%')

How to do a case sensitive search in WHERE clause (I'm using SQL Server)?

Can be done via changing the Collation. By default it is case insensitive.

Excerpt from the link:

SELECT 1
FROM dbo.Customers
WHERE   CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS
    AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS

Or, change the columns to be case sensitive.

How to make EXCEPT clause case insensitive in T-SQL

You could use case-insensitive COLLATION:

Select Col1 COLLATE Latin1_General_CI_AS From TABLE_1
Except
Select Col1 COLLATE Latin1_General_CI_AS From TABLE_2

TSQL Comparing Strings/Varchars while Ignoring Case/Capitalization?

SQL Server be default ignores case so someone has changed the setting ot youdidn't know

If it is now case sensitive, just use UPPER and forget collation

CASE   
  WHEN UPPER(Name) = 'BOND' THEN 16   
 END

Skip/Ignore a condition in CASE statement in SQL

If you want the variable you are creating with CASE expression to be NULL then use the appropriate null or missing value in the CASE expression.

Perhaps you mean you want to create the new colB variable to have the existing colB variable's value when it is not P or Q?

select colA
     , case
         when colB = 'P' then colC 
         when colB = 'Q' then colD
         else colB
       end as new_colB
from table_A

If you don't want the observations with COLB='R' in the results then exclude those using WHERE.

select colA
     , case
         when colB = 'P' then colC 
         when colB = 'Q' then colD
         else colB
       end as new_colB
from table_A
where colB ne 'R'

If you are actually using SAS then skip the SQL completely and just write SAS code to do whatever you want. Then you could actually have statements that are executed conditionally.

data want;
  set mylib.table_A;
  if colB='P' then do;
     * some other data step statements ;
  end;
run;

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)&"%'"

Making a view case insensitive for a case sensitive table

Just add COLLATE SQL_Latin1_General_CP1_CI_AS to the columns coming from the remote table.

CREATE VIEW [dbo].[myView] (
   TextColumn1, 
   Column2, 
   TextColumn3)
AS 

   SELECT 
      t.TextColumn1 COLLATE SQL_Latin1_General_CP1_CI_AS,
      t.Column2,
      t.TextColumn3 COLLATE SQL_Latin1_General_CP1_CI_AS
   FROM 
      RemoteServer.dbo.REMOTE_TABLE AS t
GO

---

Reference:

COLLATE (Transact-SQL)

---

Related Topics