How to match a CONCATENATED value to values in a temp table

Question

The database I’m working with is SQL Server 2022.

I have a table with item numbers like 1010080PC which ends with a C. I also have items like 1010080 or 1010080P (without the C at the end) and I want to add the item 1010080PC to the last column GuaranteeItem if the item number in ItemNr does not end with a C

MATNR	GuaranteeItem
1010080	1010080PC
1010080P	1010080PC
1010080PC	(empty)

As item 1010080PC already ends with a C, I do not want to enter anything in the last column.

The table has around 170,000 rows and of these rows approx. 1,700 have an item that ends with a C. I expect that around 3,400 rows have to be edited

This is what I want the result to be:

MATNR	GuaranteeItem
1010010
1010021
1010031P	1010031PC
1010031PC
1010040
1010061
1010070
1010080	1010080PC
1010080PC
1011010
1011021
1011030
1017511
1017530
1017531
1017592	1017592PC
1017592P	1017592PC
1017592PC
1017611
1017821
1017941
1017951
1017961	1017961PC
1017961P	1017961PC
1017961PC
1017981

I’ve been fighting with this for a while but I’ve not been able to find a working solution. What I’ve done is the following

I’ve made a TempTable with only the ItemNr that end with a ‘C’

SELECT DISTINCT
    prd.MARA.MATNR
    INTO prd.TempTable
FROM 
    prd.MARA
WHERE
prd.MARA.MATNR LIKE '%C'

Next I try to match the ItemNr + ‘%C’ to the ItemNr in the temp table. If the the ItemNr + ‘%C’ exists in the temp table then I add the ItemNr from the temp table to the column GuaranteeItem

SELECT DISTINCT
    prd.MARA.MATNR
    , CASE
        WHEN CONCAT(prd.MARA.MATNR, '%C') IN (SELECT prd.TempTable.MATNR FROM prd.TempTable) THEN prd.TempTable.MATNR
        WHEN prd.MARA.MATNR LIKE '%C' THEN '' 
        ELSE ''
        END AS GuaranteeItem
FROM
    prd.MARA
LEFT JOIN
    prd.TempTable ON prd.TempTable.MATNR = prd.MARA.MATNR

Somehow I can’t match WHEN CONCAT(prd.MARA.MATNR, '%C') IN (SELECT prd.TempTable.MATNR FROM prd.TempTable) THEN

Any advice to solve this would be very much appreciated.

Answer 1

You can use the EXISTS operator within a case expression as the following:

SELECT MATNR,  
  CASE 
    WHEN
      EXISTS
        (
          SELECT 1 FROM tbl_name D
          WHERE D.MATNR = CONCAT(T.MATNR, 'PC') OR
                D.MATNR = CONCAT(T.MATNR, 'C')
        )
    THEN CONCAT(T.MATNR, IIF(RIGHT(T.MATNR, 1) = 'P', 'C','PC')) 
  ELSE '' END AS GuaranteeItem
FROM tbl_name T

Demo