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

Using Case to match strings in sql server?

提问by user1368436

I am trying to use CASEin 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在 SQL Select 语句中使用该语句，该语句将允许我获得可以利用一个字符串的长度生成另一个字符串的结果的结果。这些用于来自两个数据集的不匹配记录，这些数据集共享一个公共 ID，但数据源不同。

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 '='.

消息 102，级别 15，状态 1，第 5 行“=”附近的语法不正确。

回答by HABO

You need to have a value for each WHEN, and ought to have an ELSE:

您需要为 each 设置一个值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

FYI: Len()doesn't return a string.

仅供参考：Len()不返回字符串。

EDIT:A SQL Server answer that addresses some of the comments might be:

编辑：解决一些评论的 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

回答by seanbun

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

更新您的查询

1. use '+' for string concat
2. len() returns int, no need to use ''
3. remove "Column1 =" in the case when condition
4. replace "" with ''

1. 使用“+”进行字符串连接
2. len() 返回 int，无需使用 ''
3. 在条件的情况下删除“Column1 =”
4. 用。。。来代替 ''

Hope this help.

希望这有帮助。