SQL how to check prefix in a cell with two prefixes

Question

I am learning SQL, and I have a table where there are specific cells with two prefixes:

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

I want to check this U:5$ after the first prefix, since I know how to check the R:8days; . So I need to check U:5$ , and then create a new column in the table.

My code is as follows:

 ;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

It?

 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%' 

Answer 2

(I fixed some errors in the column names and reorganized the query.)

I believe that you are looking for something like this:

 WITH CTE AS ( SELECT Employer, AmountPaid, Paid FROM Data LEFT JOIN (VALUES (4, 5, 9, 10, 15, 20)) V(Paid) ON Note LIKE '%U:' + CAST(Paid AS VARCHAR(10)) + '$;%' WHERE TypeOfTransaction LIKE 'Offline Prepaid%' AND Note LIKE '%R:8%' ) SELECT Employer, ISNULL([4.00], 0) AS [4.00], ISNULL([5.00], 0) AS [5.00], ISNULL([9.00], 0) AS [9.00], ISNULL([10.00], 0) AS [10.00], ISNULL([15.00], 0) AS [15.00], ISNULL([4.00] + [5.00] + [9.00] + [10.00] + [15.00], 0) AS Sum FROM CTE PIVOT( SUM(AmountPaid) FOR Paid IN ([4.00],[5.00],[9.00], [10.00], [15.00], [20.00])) PVT;