By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Data (String VARCHAR(1000))
INSERT Data
VALUES
('UNLABELLEDa ABCD EFGH IJKLM FOO BAR, STORE AT 15-30°C.'),
('STORE UP TO 30°C (86°F). PROTECT FROM MOISTURE'),
('MEPO BKHGT FOO BARFOO BAGK VIAL. PROTECT FROM LIGHT, STORE BETWEEN X°C TO Y°C.'),
('CARTON CONTAINING 1 X VIAL SB245063 100MG LYOPHILISED POWDER FOR RECONSTITUION. STORE AT 2°C - 8°C. PROTECT FROM LIGHT.'),
('Just a description here'),
('Finish restorer'), -- False matches
('Paint protection'), -- False matches
('Bananas. Discard after 90 days.'), -- Another keyword?
(''),
(NULL)
10 rows affected
-- Use LEAST() to chose the leftmost trim point. (Available in SQL Server 2022 and later)
-- NULLIF() is used to map a not-found position = 0 to null, so it will be ignored.
-- LEN() is added as a no-keyword-found alternative.
-- CHARINDEX() results are adjusted by - 1. LEN() is not adjusted.
SELECT
NULLIF(CHARINDEX('STORE', D.String), 0) AS Pos1,
NULLIF(CHARINDEX('PROTECT', D.String), 0) AS Pos1,
LEN(D.String) AS L, -- this is one less than the others
LEAST(
NULLIF(CHARINDEX('STORE', D.String), 0) - 1,
NULLIF(CHARINDEX('PROTECT', D.String), 0) - 1,
LEN(D.String)
) AS TrimPos,
LEFT(D.String, LEAST(
NULLIF(CHARINDEX('STORE', D.String), 0) - 1,
NULLIF(CHARINDEX('PROTECT', D.String), 0) - 1,
LEN(D.String)
)) AS Result,
STUFF(D.String, 1, LEAST(
NULLIF(CHARINDEX('STORE', D.String), 0) - 1,
NULLIF(CHARINDEX('PROTECT', D.String), 0) - 1,
LEN(D.String)
), '') AS Tail
FROM DATA D
Msg 195 Level 15 State 10 Line 9
'LEAST' is not a recognized built-in function name.
-- A CROSS APPLY can be used to separate the length calculation for aestetic reasons.
-- This makes the final select less cluttered.
-- (Several of the other intermediate results have also been removed.)
SELECT
A.TrimPos,
LEFT(D.String, A.TrimPos) AS Result,
STUFF(D.String, 1, A.TrimPos, '') AS Tail
FROM DATA D
CROSS APPLY (
SELECT LEAST(
NULLIF(CHARINDEX('STORE', D.String), 0) - 1,
NULLIF(CHARINDEX('PROTECT', D.String), 0) - 1,
LEN(D.String)
) AS TrimPos
) A
Msg 195 Level 15 State 10 Line 10
'LEAST' is not a recognized built-in function name.
-- This works with earlier SQL versions that don't have the LEAST() function.
SELECT
A.TrimPos,
LEFT(D.String, A.TrimPos) AS Result,
STUFF(D.String, 1, A.TrimPos, '') AS Tail
FROM DATA D
CROSS APPLY (
SELECT MIN(Pos) AS TrimPos
FROM (
VALUES
(NULLIF(CHARINDEX('STORE', D.String), 0) - 1),
(NULLIF(CHARINDEX('PROTECT', D.String), 0) - 1),
(LEN(D.String))
) V(Pos)
) A
| TrimPos | Result | Tail |
|---|---|---|
| 37 | UNLABELLEDa ABCD EFGH IJKLM FOO BAR, | STORE AT 15-30°C. |
| 0 | STORE UP TO 30°C (86°F). PROTECT FROM MOISTURE | |
| 33 | MEPO BKHGT FOO BARFOO BAGK VIAL. | PROTECT FROM LIGHT, STORE BETWEEN X°C TO Y°C. |
| 80 | CARTON CONTAINING 1 X VIAL SB245063 100MG LYOPHILISED POWDER FOR RECONSTITUION. | STORE AT 2°C - 8°C. PROTECT FROM LIGHT. |
| 23 | Just a description here | |
| 9 | Finish re | storer |
| 6 | Paint | protection |
| 31 | Bananas. Discard after 90 days. | |
| 0 | null | |
| null | null | null |
Warning: Null value is eliminated by an aggregate or other SET operation.
-- We can also use a table of keywords, which allows us to add more as needed.
CREATE TABLE Keywords (Keyword VARCHAR(100))
INSERT Keywords VALUES ('STORE'), ('PROTECT'), ('DISCARD')
3 rows affected
-- Using a subselect to find the leftmost trim point based on keyword table.
-- We still have some false matches for keywords appearing in the middle of words.
SELECT
A.TrimPos,
LEFT(D.String, A.TrimPos) AS Result,
STUFF(D.String, 1, A.TrimPos, '') AS Tail
FROM Data D
CROSS APPLY (
SELECT ISNULL(MIN(P.Pos), LEN(D.String)) AS TrimPos
FROM (
SELECT NULLIF(CHARINDEX(K.Keyword, D.String), 0) - 1 AS Pos
FROM Keywords K
) P
) A
| TrimPos | Result | Tail |
|---|---|---|
| 37 | UNLABELLEDa ABCD EFGH IJKLM FOO BAR, | STORE AT 15-30°C. |
| 0 | STORE UP TO 30°C (86°F). PROTECT FROM MOISTURE | |
| 33 | MEPO BKHGT FOO BARFOO BAGK VIAL. | PROTECT FROM LIGHT, STORE BETWEEN X°C TO Y°C. |
| 80 | CARTON CONTAINING 1 X VIAL SB245063 100MG LYOPHILISED POWDER FOR RECONSTITUION. | STORE AT 2°C - 8°C. PROTECT FROM LIGHT. |
| 23 | Just a description here | |
| 9 | Finish re | storer |
| 6 | Paint | protection |
| 9 | Bananas. | Discard after 90 days. |
| 0 | null | |
| null | null | null |
Warning: Null value is eliminated by an aggregate or other SET operation.
-- This variation uses PATINDEX() and the [^A-Z] (not a letter) prefix/postfix
-- to limit matches to whole words. (An adjustment is needed to handle the case
-- where a match is at the string start.)
SELECT
A.TrimPos,
LEFT(D.String, A.TrimPos) AS Result,
STUFF(D.String, 1, A.TrimPos, '') AS Tail
FROM Data D
CROSS APPLY (
SELECT ISNULL(MIN(P2.AdjustedPos), LEN(D.String)) AS TrimPos
FROM (
SELECT NULLIF(PATINDEX('%[^A-Z]' + K.Keyword + '[^A-Z]%', ' ' + D.String + ' '), 0) - 2 AS Pos
FROM Keywords K
) P
CROSS APPLY (
SELECT CASE WHEN P.Pos < 0 THEN 0 ELSE P.Pos END AS AdjustedPos
) P2
) A
| TrimPos | Result | Tail |
|---|---|---|
| 36 | UNLABELLEDa ABCD EFGH IJKLM FOO BAR, | STORE AT 15-30°C. |
| 0 | STORE UP TO 30°C (86°F). PROTECT FROM MOISTURE | |
| 32 | MEPO BKHGT FOO BARFOO BAGK VIAL. | PROTECT FROM LIGHT, STORE BETWEEN X°C TO Y°C. |
| 79 | CARTON CONTAINING 1 X VIAL SB245063 100MG LYOPHILISED POWDER FOR RECONSTITUION. | STORE AT 2°C - 8°C. PROTECT FROM LIGHT. |
| 23 | Just a description here | |
| 15 | Finish restorer | |
| 16 | Paint protection | |
| 8 | Bananas. | Discard after 90 days. |
| 0 | null | |
| null | null | null |
Warning: Null value is eliminated by an aggregate or other SET operation.