SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Concatcleanup(
    Machine       VARCHAR(30), 	
    Product 	  VARCHAR(30),
    Sources       VARCHAR(30)
);
INSERT INTO Concatcleanup VALUES
('M3', 	'H', 	'cmdd6'),
('M3', 	'H', 	   '91'),
('M3', 	'H', 	'cmdd3'),
('M4', 	'I', 	'cmdd7'),
('M4', 	'J', 	'cmdd7'),
('M4', 	'B', 	  '827'),
('M4', 	'B', 	'cmdd7');

SELECT * FROM Concatcleanup


Machine
Product
Sources


M3
H
cmdd6

M3
H
91

M3
H
cmdd3

M4
I
cmdd7

M4
J
cmdd7

M4
B
827

M4
B
cmdd7


WITH CTE(Machine, Product,Sources, duplicatecount) AS (
    SELECT Machine, Product, Sources,  
           ROW_NUMBER() OVER(
               PARTITION BY Machine, Product
               ORDER BY CASE WHEN Sources NOT LIKE 'cmdd%' THEN 1 END,
                        Sources
           ) AS DuplicateCount                 
    FROM Concatcleanup
)
DELETE FROM cte
WHERE duplicatecount > 1
3 rows affected

SELECT * FROM Concatcleanup;


Machine
Product
Sources


M3
H
cmdd3

M4
I
cmdd7

M4
J
cmdd7

M4
B
cmdd7


SELECT Machine, Product, Sources,  
       ROW_NUMBER() OVER(
           PARTITION BY Machine, Product
           ORDER BY CASE WHEN Sources NOT LIKE 'cmdd%' THEN 1 END,
                    Sources
       ) AS DuplicateCount
INTO newtab
FROM Concatcleanup;

SELECT * FROM newtab


Machine
Product
Sources
DuplicateCount


M3
H
cmdd3
1

M4
B
cmdd7
1

M4
I
cmdd7
1

M4
J
cmdd7
1