Sql 2005 How to Use Keyword Like in a Case Statement

SQL 2005 Can I use keyword like in a case statement

try this

SELECT CASE 
           WHEN ColumnName = 'value1' THEN 'answer1'
           WHEN ColumnName = 'value2' THEN 'answer2'
           WHEN ColumnName LIKE '%TEST%' THEN 'answer3'
       END AS Answer
FROM TableName

example you can run

SELECT name,CASE 
           WHEN Name = 'sysobjects' THEN 'answer1'
           WHEN Name = 'syscols' THEN 'answer2'
           WHEN Name LIKE '%p%' THEN 'answer3'
           ELSE 'unknown'
       END AS Answer
FROM sysobjects

SELECT CASE with LIKE statement sql

Microsoft Access does not support the CASE...WHEN statement. Use the IIF statement instead:

IIF(expression, value if true, value if false)

reference

SELECT tblFollowUpQs.AdditionalHealthProblems, 
IIf(tblFollowUpQs.AdditionalHealthProblems Like "IBS",1,0) AS [Diagnosed] 
FROM tblFollowUpQs
WHERE (((tblFollowUpQs.AdditionalHealthProblems) Like "'IBS"));

If you need to use wildcard characters you can read more on office.microsoft.com

SQL Case Statement Proper Aliasing

This really has nothing to do with the CASE statement. You can create a column alias for any expression in the select list, including simple column names (ie: to re-name to match client code), literal expressions, function calls, etc. But continuing with your CASE example, there are some things to know.

I almost never see it this way, but the ANSI Sql standard says to use double quotes for the name, like so:

SELECT CASE WHEN ... THEN ... END "isItemDisplayed" FROM myTable

What I do commonly see are the 3rd or 5th options from your original question, shown below. Either is fine:

SELECT CASE WHEN ... THEN ... END As isItemDisplayed FROM myTable

SELECT CASE WHEN ... THEN ... END isItemDisplayed FROM myTable

Either of these also allow you to encase the names in square-brackets ([]) if you want a name that conflicts with a reserved word or uses spaces. I would avoid any that use the = syntax, as it could confuse someone that you're looking for a boolean result. The other bit advice I can give here is to pick one style and stick with it in a current environment. Consistency!

I want to use CASE statement to update some records in sql server 2005

Add a WHERE clause

UPDATE dbo.TestStudents  
SET     LASTNAME =  CASE  
                        WHEN LASTNAME = 'AAA' THEN 'BBB' 
                        WHEN LASTNAME = 'CCC' THEN 'DDD' 
                        WHEN LASTNAME = 'EEE' THEN 'FFF' 
                        ELSE LASTNAME
                    END 
WHERE   LASTNAME IN ('AAA', 'CCC', 'EEE')

SQL Server 2005 : using OR operator with LIKE operator

select * 
from table where 
column_name like '%boo%' 
or column_name like '%bar%'  

You need to repeat what the condition is being tested on. In this case, you need to repeat column_name in your second conditional like clause.

The like or any conditional test does not persist from the previous statement/test.

SQL LEFT JOIN with a CASE Statement

You can't compare null values like other values. Use the is null operator in the case:

'Enable' = CASE WHEN hid.ID IS NULL THEN 'true' ELSE 'false' END

CASE (Contains) rather than equal statement

CASE WHEN ', ' + dbo.Table.Column +',' LIKE '%, lactulose,%' 
  THEN 'BP Medication' ELSE '' END AS [BP Medication]

The leading ', ' and trailing ',' are added so that you can handle the match regardless of where it is in the string (first entry, last entry, or anywhere in between).

That said, why are you storing data you want to search on as a comma-separated string? This violates all kinds of forms and best practices. You should consider normalizing your schema.

In addition: don't use 'single quotes' as identifier delimiters; this syntax is deprecated. Use [square brackets] (preferred) or "double quotes" if you must. See "string literals as column aliases" here: http://msdn.microsoft.com/en-us/library/bb510662%28SQL.100%29.aspx

EDIT If you have multiple values, you can do this (you can't short-hand this with the other CASE syntax variant or by using something like IN()):

CASE 
  WHEN ', ' + dbo.Table.Column +',' LIKE '%, lactulose,%' 
  WHEN ', ' + dbo.Table.Column +',' LIKE '%, amlodipine,%' 
  THEN 'BP Medication' ELSE '' END AS [BP Medication]

If you have more values, it might be worthwhile to use a split function, e.g.

USE tempdb;
GO

CREATE FUNCTION dbo.SplitStrings(@List NVARCHAR(MAX))
RETURNS TABLE
AS
   RETURN ( SELECT DISTINCT Item FROM
       ( SELECT Item = x.i.value('(./text())[1]', 'nvarchar(max)')
         FROM ( SELECT [XML] = CONVERT(XML, '<i>'
         + REPLACE(@List,',', '</i><i>') + '</i>').query('.')
           ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
       WHERE Item IS NOT NULL
   );
GO

CREATE TABLE dbo.[Table](ID INT, [Column] VARCHAR(255));
GO

INSERT dbo.[Table] VALUES
(1,'lactulose, Lasix (furosemide), oxazepam, propranolol, rabeprazole, sertraline,'),
(2,'lactulite, Lasix (furosemide), lactulose, propranolol, rabeprazole, sertraline,'),
(3,'lactulite, Lasix (furosemide), oxazepam, propranolol, rabeprazole, sertraline,'),
(4,'lactulite, Lasix (furosemide), lactulose, amlodipine, rabeprazole, sertraline,');

SELECT t.ID
  FROM dbo.[Table] AS t
  INNER JOIN dbo.SplitStrings('lactulose,amlodipine') AS s
  ON ', ' + t.[Column] + ',' LIKE '%, ' + s.Item + ',%'
  GROUP BY t.ID;
GO

Results:

ID
----
1
2
4

Is the LIKE operator case-sensitive with SQL Server?

It is not the operator that is case sensitive, it is the column itself.

When a SQL Server installation is performed a default collation is chosen to the instance. Unless explicitly mentioned otherwise (check the collate clause bellow) when a new database is created it inherits the collation from the instance and when a new column is created it inherits the collation from the database it belongs.

A collation like sql_latin1_general_cp1_ci_as dictates how the content of the column should be treated. CI stands for case insensitive and AS stands for accent sensitive.

A complete list of collations is available at https://msdn.microsoft.com/en-us/library/ms144250(v=sql.105).aspx

(a) To check a instance collation

select serverproperty('collation')

(b) To check a database collation

select databasepropertyex('databasename', 'collation') sqlcollation

(c) To create a database using a different collation

create database exampledatabase
collate sql_latin1_general_cp1_cs_as 

(d) To create a column using a different collation

create table exampletable (
    examplecolumn varchar(10) collate sql_latin1_general_cp1_ci_as null
)

(e) To modify a column collation

alter table exampletable
alter column examplecolumn varchar(10) collate sql_latin1_general_cp1_ci_as null

It is possible to change a instance and database collations but it does not affect previously created objects.

It is also possible to change a column collation on the fly for string comparison, but this is highly unrecommended in a production environment because it is extremely costly.

select
  column1 collate sql_latin1_general_cp1_ci_as as column1
from table1

---

Related Topics