SQL Server 2014

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.
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