sql-server - SQL如何检查带有两个前缀的单元格中的前缀

Question

我正在学习 SQL 并且我有一个表，其中某些单元格带有两个前缀，如下所示:

example1(cell) : R:8days; U:5$;
example2(cell) : R:8days;
example3(cell) : U:5$;

我想检查一下 U:5$在第一个前缀之后，我知道如何检查前缀 R:8days; .所以我需要检查 U:5$然后在表中创建一个新列。

我的代码如下所示:

;with cte as (
select
Employer, AmountPayd, AmountPayd as Payd
from data
where TipeOfTransaction like 'Offline Prepaid%' AND Note like '%R:8%' **HERE I WANT TO CHECK FOR PREFIX NR2. 'U:5$' AND MAKE NEW COLUMN FOR WHICH EMPLOYER HAS U:5$ NOTE.**
)
select
Employer,
     [4.00] = ISNULL([4.00],0)
    ,[5.00] = ISNULL([5.00],0)
    ,[9.00] = ISNULL([9.00],0)
    ,[10.00] = ISNULL([10.00],0)
    ,[15.00] = ISNULL([15.00],0)
    ,[Sum] =ISNULL([4.00],0) + ISNULL([5.00],0) + ISNULL([9.00],0) + ISNULL([10.00],0) + ISNULL([15.00],0)
    from cte
    pivot (
    sum(AmountPayd) for Payd in ([4.00],[5.00],[9.00], [10.00], [15.00], [20.00]))pvt;

Answer 1

这个？

select
  Employer, AmountPayd, AmountPayd as Payd,
  CASE WHEN Note like '%R:8%;%U:5$%' THEN 'U:5' END U5Note
from data
where TipeOfTransaction like 'Offline Prepaid%' AND Note like '%R:8%'