Using Case to match strings in sql server?
40,185
Solution 1
You need to have a value for each WHEN
, and ought to have an ELSE
:
Select Data_Source, CustomerID,
CASE
WHEN Data_Source = 'Test1' and Len(CustomerName) = 35 THEN 'First Value'
WHEN Data_Source = 'Test2' THEN substring(CustomerName, 1, 35)
ELSE 'Sorry, no match.'
END AS CustomerName
From dbo.xx
FYI: Len()
doesn't return a string.
EDIT: A SQL Server answer that addresses some of the comments might be:
declare @DataSource as Table ( Id Int Identity, CustomerName VarChar(64) )
declare @VariantDataSource as Table ( Id Int Identity, CostumerName VarChar(64) )
insert into @DataSource ( CustomerName ) values ( 'Alice B.' ), ( 'Bob C.' ), ( 'Charles D.' )
insert into @VariantDataSource ( CostumerName ) values ( 'Blush' ), ( 'Dye' ), ( 'Pancake Base' )
select *,
-- Output the CostumerName padded or trimmed to the same length as CustomerName. NULLs are not handled gracefully.
Substring( CostumerName + Replicate( '.', Len( CustomerName ) ), 1, Len( CustomerName ) ) as Clustermere,
-- Output the CostumerName padded or trimmed to the same length as CustomerName. NULLs in CustomerName are explicitly handled.
case
when CustomerName is NULL then ''
when Len( CustomerName ) > Len( CostumerName ) then Substring( CostumerName, 1, Len( CustomerName ) )
else Substring( CostumerName + Replicate( '.', Len( CustomerName ) ), 1, Len( CustomerName ) )
end as 'Crustymore'
from @DataSource as DS inner join
@VariantDataSource as VDS on VDS.Id = DS.Id
Solution 2
Select
Column1,
Column2,
Case
When Column1 = 'Something' and Len(Column2) = 35
Then 'Something Else' + substring(Column2, 1, 35)
End as Column3
From dbo.xxx
Update your query on
- use '+' for string concat
- len() returns int, no need to use ''
- remove "Column1 =" in the case when condition
- replace "" with ''
Hope this help.
Author by
Admin
Updated on July 09, 2022Comments
-
Admin almost 2 years
I am trying to use
CASE
in a SQL Select statement that will allow me to get results where I can utilize the length of one string to produce the resutls of another string. These are for non-matched records from two data sets that share a common ID, but variant Data Source.Case statement is below:
Select Column1, Column2, Case When Column1 = 'Something" and Len(Column2) = '35' Then Column1 = "Something Else" and substring(Column2, 1, 35) End as Column3 From dbo.xxx
When I run it I get the following error:
Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '='.