CASE expressions can be used in SQL anywhere an expression can be used. Example of where CASE expressions can be used include in the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and inside of built-in functions.
Two basic formulations for CASE expression
1) Simple CASE expressions
A simple CASE expression checks one expression against multiple values. Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. A simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.
Syntax:
CASE expression
WHEN expression1 THEN expression1
[[WHEN expression2 THEN expression2] [...]]
[ELSE expressionN]
END
Example:
DECLARE @TestVal INT
SET @TestVal = 3
SELECT
CASE @TestVal
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
ELSE 'Other'
END
2) Searched CASE expressions
A searched CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean expression. The simple CASE expression checks only for equivalent values and can not contain Boolean expressions. The basic syntax for a searched CASE expressions is shown below:
Syntax:
CASE
WHEN Boolean_expression1 THEN expression1
[[WHEN Boolean_expression2 THEN expression2] [...]]
[ELSE expressionN]
END
Example:
DECLARE @TestVal INT
SET @TestVal = 5
SELECT
CASE
WHEN @TestVal <=3 THEN 'Top 3'
ELSE 'Other'
END
Reference : Pinal Dave (https://blog.sqlauthority.com)
149 Comments. Leave new
I am attempting to update table1.status with a 1 if it exists within table2 otherwise i want it to update with a 0 if it does not. the statement I have is as follows
Update
[table1]
SET Status = (
case
when
(Select status from table1 inner join table2
on table1.ID = table2.ID)
then ‘1’
else ‘0’
you need this at the end of your code
end
)
I am getting problem in removing special character from string
I am doing like this :I want to do multiple sepcial charcter remove from this but i am able to replace only on charcter
declare @name varchar(10)
declare @currentchar varchar(10)
declare @backUp varchar(10)
declare @len int
declare @i int
set @name=’;kri;pal’
set @len=len(@name)
set @i=1
declare @Kk varchar(20)
WHILE @i <= @len
BEGIN
SET @currentchar = SUBSTRING(@name,@i, 1)
select
@backUp =(case when (@currentchar=';' )then
replace(@currentchar,';' ,'')
else
@currentchar end)
set @kk=isnull(@kk,'')+@backUp
SET @i = @i + 1
END
pls help me urgent:
Refer this post and use the approach described there
@krish
Why not use REPLACE() for the entire string? No WHILE loop or anything, just a simple REPLACE(@name, ‘;’, ”)
Sub: CASE Statement in SQL server
Can you please tell me that
CASE Statement working with String / Varchar
means i want to check some varchar values
DECLARE @TestVal varchar
SET @TestVal = ‘Asma’
SELECT
CASE
WHEN @TestVal =’Asma’ THEN ‘Top 1’
ELSE ‘Other’
END
@Asma,
Ofcourse it will work.
Tejas
@Asma Qureshi
What is the problem?
Also, you can use a simple case expression:
DECLARE @TestVal varchar
SET @TestVal = ‘Asma’
SELECT
CASE @TestVal
WHEN ‘Asma’ THEN ‘Top 1’
ELSE ‘Other’
END
Hi Experts,
Is anyway i can write following query in Case statement? please guide me
(
( ( @Variable(‘EntityType’) = ‘DEPARTMENT’ ) AND ( @Variable(‘EntityCode’) = ‘–ALL–‘ ) )
OR ( (@Variable(‘EntityType’) = ‘DEPARTMENT’) AND(@Variable(‘EntityCode’) != ‘–ALL–‘ ) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.DEPT_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
OR ((@Variable(‘EntityType’) = ‘RAILROAD’) AND (@Variable(‘EntityCode’) = ‘–ALL–‘))
OR ((@Variable(‘EntityType’) = ‘RAILROAD’) AND (@Variable(‘EntityCode’) !=’–ALL–‘) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.RAILROAD_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
OR ((@Variable(‘EntityType’) = ‘DIVISION’) AND (@Variable(‘EntityCode’) = ‘–ALL–‘))
OR ((@Variable(‘EntityType’) = ‘DIVISION’) AND (@Variable(‘EntityCode’) !=’–ALL–‘) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.DIVISION_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
OR ((@Variable(‘EntityType’) = ‘FUNCTION’) AND (@Variable(‘EntityCode’) = ‘–ALL–‘))
OR ((@Variable(‘EntityType’) = ‘FUNCTION’) AND (@Variable(‘EntityCode’) !=’–ALL–‘) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.FUNCTION_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
OR ((@Variable(‘EntityType’) = ‘SUBFUNCTION’) AND (@Variable(‘EntityCode’) = ‘–ALL–‘))
OR ((@Variable(‘EntityType’) = ‘SUBFUNCTION’) AND (@Variable(‘EntityCode’) !=’–ALL–‘) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.SUBFUNCTION_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
OR ((@Variable(‘EntityType’) = ‘RESCEN’) AND (@Variable(‘EntityCode’) = ‘–ALL–‘))
OR ((@Variable(‘EntityType’) = ‘RESCEN’) AND (@Variable(‘EntityCode’) !=’–ALL–‘) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.RESCEN_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
)
Thank you
PV
@PV
How’s this?
WHERE
@Variable(‘EntityCode’) = ‘–ALL–’
OR LTRIM
(
RTRIM
(
CASE (@Variable(‘EntityType’)
WHEN ‘DEPARTMENT’ THEN Alias_dbo_SAF_ORG_STRUCTURE.DEPT_CD
WHEN ‘RAILROAD’ THEN Alias_dbo_SAF_ORG_STRUCTURE.RAILROAD_CD
WHEN ‘DIVISION’ THEN Alias_dbo_SAF_ORG_STRUCTURE.DIVISION_CD
WHEN ‘FUNCTION’ THEN Alias_dbo_SAF_ORG_STRUCTURE.FUNCTION_CD
WHEN ‘SUBFUNCTION’ THEN Alias_dbo_SAF_ORG_STRUCTURE.SUBFUNCTION_CD
WHEN ‘RESCEN’ THEN Alias_dbo_SAF_ORG_STRUCTURE.RESCEN_CD
END
)
) IN (SELECT * FROM ufn_multi_value_to_tbl(@Variable(‘EntityCode’)
Hello Experts, i want to set a variable and use the value later for processing, i am getting an error when using the below..
SELECT
CASE @TestVal
WHEN 1 THEN set @var= ‘First’
WHEN 2 THEN Set @var= ‘Second’
WHEN 3 THEN set @var= ‘Third’
ELSE ‘Other’
END
===================
insert into table (@var, etc….)
Hi Anil,
Correct syntax to assign a value to variable with CASE statement is:
SELECT @var=
CASE @TestVal
WHEN 1 THEN set ‘First’
WHEN 2 THEN Set ‘Second’
WHEN 3 THEN set ‘Third’
ELSE ‘Other’
END
Thanks,
Tejas
SQLYoga.com
Hello Anil,
Use the SELECT CASE as below:
SELECT @var =
CASE @TestVal
WHEN 1 THEN ‘First’
WHEN 2 THEN ‘Second’
WHEN 3 THEN ‘Third’
ELSE ‘Other’
END
Regards,
Pinal Dave
Dear All
Kindly correct the given below part of query.
SELECT distinct(P1.DOCNUM),P6.DOCENTRY,
Case when P1.FOOTER = NULL THEN
P1.U_POTRD , P1.U_EXARW , P1.U_ConNum as ContainerNo, P1.U_Vessel_Flight_no as VesselNo, ,P1.U_CDIBOL as BLNumber, cast(P1.FOOTER as varchar(max))as FOOTER ,
ELSE
P5.U_POTRD , P5.U_EXARW , P5.U_ConNum as ContainerNo, P5.U_Vessel_Flight_no as VesselNo, ,P5.U_CDIBOL as BLNumber, cast(P5.FOOTER as varchar(max))as FOOTER,
Endcase
——-
Problem with case part.
With Regards,
Nirbhay
Use
P1.FOOTER IS NULL
instead of
P1.FOOTER = NULL
I’m a novice (if that). I’m trying to use MS Query to send data to Excel and I’m only trying to pull ACTNCNT when ACTNCNT is >0, ELSE ESTNCNT.
In Excel it would be =IF (ACTNCNT > 0, ACTNCNT, ESTNCNT). In Crystal, I would be able to create a formula field I could show the output for – NCNT; IIF (ACTNCNT > 0, ACTNCNT, ESTNCNT).
Is there anyway to pass “NCNT” to Excel? Or do I need to pull both and run a macro?
I’m also having issue using a parameter. When I’m in MS Query I can use OCCURRED>=[From Date]AND=#1/28/2010 6:00:00 AM# And <=#1/29/2010 6:00:00 AM#"; everything works.
Any thoughts?
@Jason
Your question is not clear.
Are you trying to export data from MS SQL Server to excel sheet. What tool are you using, like SSIS/DTS/openquery ?
Writing a case statement in DTS/SSIS data flow task is very easy.
Please explain clearly what is your requirement.
~ IM
Im trying to use 2 CASE statements together in a SELECT query without much luck.
The second CASE uses the output from the first CASE as it’s expression (well it’s supposed to).
SELECT
accountNum
, balanceCalculation = case WHEN Balance > 5000 THEN Balance *2
ELSE Balance /2
END
, balanceDescription = CASE WHEN balanceCalculation > 6000 THEN ‘Large Balance’
ELSE ‘small balance’
END
FROM tblFinances
@Ross
Check if this helps.
SELECT accountNum
, balanceCalculation = CASE
WHEN Balance > 5000 THEN Balance *2
ELSE Balance /2
END
, balanceDescription = CASE WHEN (
CASE
WHEN Balance > 5000 THEN Balance *2
ELSE Balance /2
END
) > 6000 THEN ‘Large Balance’
ELSE ’small balance’
END
FROM tblFinances
~ IM.
How do I use case when Im want to compare not only the column under question but also another column that has a said value.
For example table a has col1, col2, col3. Now when I want to see what is stored in Col1 in conjunction with Col2, how do I do that.
For example
Col1 Col2 Col3
1 5 44
2 6 12
3 ” 1
3 A 3
Now here, I want to update the value of Col3 under the following conditions –
1. When Col3 value = 1 and
2. When Col2 value = Null then set the value of Col3 to Null.
How would I do this ?
Thanks,
Tim K Liu
@Tim k
SET Col3 = CASE WHEN Col3 = 1 AND Col2 IS NULL THEN NULL ELSE Col3 END
Or, the inverse:
SET Col3 = CASE WHEN Col3 1 AND Col2 IS NOT NULL THEN Col3 END
Although, this could usually be done in ther WHERE clause:
UPDATE mytab SET Col3 = NULL WHERE Col3 = 1 AND Col2 IS NULL;
Thank You Brian Tkatch !
I figured the second one by myself but I followed your firststep as it is a bit more readable & understandable.
@Tim
Yeah, that’s why i put it there. :) The second one is cleaner, the first one is easier.
Note that the third option is probably best, as the CASE will UPDATE every single record, either to NULL or to itself. If it is in the WHERE clause, it will only UPDATE the records that need changing.
This is beneficial because:
1) In the first case an AFTER UPDATE TRIGGER will fire even if the record is SET to itself.
2) For a large TABLE, setting a small subset is usually faster. The CASE would UPDATE every record, so it might take longer.
3) If another process has the row locked, and this UPDATE needs to change the value, that is fine. But if it doesn’t need to change it, and it just sets it to itself, the CASE can cause a wait until the record is no longer locked (or timesout).
hi am trying to do a consultation with case and brand me a mistake, could you help please???
excuse the grammar, do not speak English
hi am trying to do a consultation with case and brand me a mistake, could you help please???
excuse the grammar, do not speak English
USE mantto1;
GO
DECLARE @DiaReporte datetime, @fir datetime, @diasR as int;
SET ANSI_WARNINGs OFF
SELECT @DiaReporte = ‘9/02/2010’, @fir=min(fa_real), @diasR=datediff (dd,actividades.fecha_i_p,@fir)
FROM avances , actividades
where actividades.clave=avances.clave
group by actividades.fecha_i_p
select case when @diasR>0 then
SELECT @DiaReporte as fecha_reporte, @fir as fecha_inicial_r, @diasR as dias_retraso
GO
sorry, this is the qerry, i have a mistake but i don’t know what is it :(
USE mantto1;
GO
DECLARE @DiaReporte datetime, @fir datetime, @diasR as int;
SET ANSI_WARNINGs OFF
SELECT @DiaReporte = ‘9/02/2010’, @fir=min(fa_real), @diasR=datediff (dd,actividades.fecha_i_p,@fir)
FROM avances , actividades
where actividades.clave=avances.clave
group by actividades.fecha_i_p
select case when @diasR>0 then
SELECT @DiaReporte as fecha_reporte, @fir as fecha_inicial_r, @diasR as dias_retraso
GO
select case when @diasR>0 then
SELECT @DiaReporte as fecha_reporte, @fir as fecha_inicial_r, @diasR as dias_retraso
Probably should be:
IF @diasR > 0 SELECT @DiaReporte AS fecha_reporte, @fir AS fecha_inicial_r, @diasR AS dias_retraso