I have a imported table of data from Excel (ImportedTable example) that has data and some "units" jumbled together and trying to "filter" out the units (LookupTable example) to clean it up. The LookupTable can have almost 20 different values, and the ImportedTable is about 2000 records.
Trying to select the text from a column without the values from another table. Example:
LookupTable:
"Id" | "Name"
'1' | '% time operational'
'2' | 'KGal'
'3' | 'Gallons'
...
ImportedTable:
"Id" | "Text"
'1' | 'SomeText here % time operational'
'2' | '500 KGal'
'3' | '1.05 Gallons'
'4' | '105,000'
'5' | 'TestTextKGal'
...
Desired Result:
'SomeText here'
'500'
'1.05'
'105,000'
'TestText'
I know this doesn't work but wanted something like this to work:
SELECT LTRIM(RTRIM(REPLACE([Text], (SELECT DISTINCT [Name] FROM LookupTable), '')))
FROM ImportedTable
And those lookup values are removed from the "Text" in every record in ImportedTable, in a select statement (not actually removing them from the table).
Cross join the two tables and take the
MIN()
of the result of making all the substitutions:See live demo.
Note that the convenient use of
MIN()
here only works when removing text from the end of text. It works becauseMIN()
considers text to be "less" if characters are removed from the end, eg'TestText'
is "less than"'TestTextKGal'
.Note also the use of
TRIM(...)
instead ofLTRIM(RTRIM(...))
.