Menu
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
519
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 .
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 .
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.
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.
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.
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?
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.
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’.
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
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
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
