By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table LookupTable (name varchar(99));
insert into LookupTable values ( '% time operational');
insert into LookupTable values ( 'KGal');
insert into LookupTable values ( 'Gallons');
create table ImportedTable (id int, name varchar(99));
insert into ImportedTable values ( 1, 'SomeText here % time operational');
insert into ImportedTable values ( 2, '500 KGal');
insert into ImportedTable values ( 3, '1.05 Gallons');
insert into ImportedTable values ( 4, '105,000');
insert into ImportedTable values ( 5, 'Gallons');
insert into ImportedTable values ( 6, 'TestTextKGal');
SELECT
i.[id], MIN(TRIM(REPLACE(i.[Name], l.[Name], ''))) AS Name
FROM ImportedTable i
CROSS JOIN LookupTable l
GROUP BY i.[id]
| id | Name |
|---|---|
| 1 | SomeText here |
| 2 | 500 |
| 3 | 1.05 |
| 4 | 105,000 |
| 5 | |
| 6 | TestText |