Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL 2005 Can I use keyword like in a case statement

I need a case statement that allows partial matches. I get a syntax error, but am wondering if anything similar to this is possible. If not, my secondary solution is to re-write as a cursor... Example:

SELECT CASE ColumnName
           WHEN 'value1' THEN 'answer1'
           WHEN 'value2' THEN 'answer2'
           WHEN LIKE '%TEST%' THEN 'answer3'
       END AS Answer
FROM TableName
like image 519
Paul Avatar asked Oct 07 '11 13:10

Paul


People also ask

Can we use like in case statement in SQL?

The CASE statement always goes in the SELECT clause. CASE must include the following components: WHEN , THEN , and END . ELSE is an optional component. You can make any conditional statement using any conditional operator (like WHERE ) between WHEN and THEN .

Can we use like in case statement in Oracle?

Introduction to Oracle CASE expressionYou can use a CASE expression in any statement or clause that accepts a valid expression. For example, you can use the CASE expression in statements such as SELECT , UPDATE , or DELETE , and in clauses like SELECT , WHERE , HAVING , and ORDDER BY .

What is like %% in SQL?

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.

Can we use aggregate function in case statement?

CASE statement in SQL and aggregate functions Aggregate functions in SQL Server perform calculations and return a single value. Examples of aggregate functions are MIN, MAX, COUNT, ABG and CHECKSUM. For this purpose, we use the COUNT aggregate function in SQL Server.

How do you write a case statement in an SQL statement?

SQL Case Statement Syntax The syntax has a lot of stuff in it, but it is still rather intuitive: the keyword CASE signals the beginning of a case statement, and the keyword END signals its end. Then for a single condition you can write the keyword WHEN followed by the condition that has to be satisfied.

Can you use case in SQL with multiple conditional statements?

If you need to evaluate multiple conditional statements, the SQL CASE statement will do the job. Here’s what you need to know to use CASE like a pro. Why is CASE so important in SQL?

How does the case statement work in Python?

The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result.

How do I use case in an UPDATE statement?

You can also use CASE in an UPDATE statement. The SQL UPDATE statement is used to change values in an existing table. Imagine that you want to update the influencer_channel values in our current dataset by changing the channels to a two letter code: ‘youtube’ has to be changed to ‘yt’ and ‘facebook’ has to be changed to ‘fb’.


2 Answers

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
like image 85
SQLMenace Avatar answered Nov 03 '22 10:11

SQLMenace


Need to use a slightly different CASE syntax:

SELECT CASE WHEN ColumnName LIKE 'value1' THEN 'answer1'
            WHEN ColumnName LIKE 'value2' THEN 'answer2'
            WHEN ColumnName LIKE '%TEST%' THEN 'answer3'
ELSE 'not like any of them' END [Answer]
FROM TableName
like image 27
Widor Avatar answered Nov 03 '22 11:11

Widor