string - 使用Case来匹配sql server中的字符串？

Question

我正在尝试在 SQL Select 语句中使用 CASE ，这将允许我获得结果，我可以利用一个字符串的长度来生成另一个字符串的结果。这些记录适用于共享公共(public) ID 但数据源不同的两个数据集中的不匹配记录。

案例陈述如下:

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

当我运行它时，出现以下错误:

Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '='.

Answer 1

您需要为每个 WHEN 提供一个值，并且应该有一个 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

仅供引用:Len() 不返回字符串。

编辑:针对某些评论的 SQL Server 答案可能是:

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