By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Table1
([Id] int, [Name] varchar(22))
;
INSERT INTO Table1
([Id], [Name])
VALUES
('1', '% time operational'),
('2', 'KGal'),
('3', 'Gallons')
;
3 rows affected
CREATE TABLE Table2
([Id] int, [Text] varchar(32))
;
INSERT INTO Table2
([Id], [Text])
VALUES
(1, 'SomeText here % time operational'),
(2, '500 KGal'),
(3, '1.05 Gallons'),
(4, '105,000'),
(5, 'TestTextKGal')
;
5 rows affected
WITH CTE aS (SELECT
t2.[id], REPLACE(t2.[Text],t1.[Name],'') resttest
FROM Table1 t1 CROSS JOIN Table2 t2
WHERE t2.[Text] LIKE CONCAT('%' , t1.[Name] , '%'))
SELECT * FROM CTE
UNION ALL
SELECT [Id],[Text] FROM Table2
WHERE [Id] NOT IN (SELECT [Id] FROM CTE )
ORDER BY Id
| id | resttest |
|---|---|
| 1 | SomeText here |
| 2 | 500 |
| 3 | 1.05 |
| 4 | 105,000 |
| 5 | TestText |