Assuming your ssms tag means you are on SQL server, you have only limited support to some version of regular expression (not the POSIX regex).
The below query will do what you want. Its principle is to locate the rightmost occurrence of LG and from there, the 2nd preceding space. The result you want is between these 2 positions.
Refer to the comments for explanation, remove the CTE included to have a complete example and cast the returned value to integer yourself if needed:
WITH MyTable(description) AS (
SELECT STUD BOLT SET, SANS 1700, GR 8.8, SANS 1700, GR 8, GALVANISED (8 X M24 X 140 LG C/W 1 NUT, 12 X M24 X 170 LG C/W 1 NUT)
UNION ALL
SELECT STUD BOLT SET, SANS 1700, GR 8.8, SANS 1700, GR 8, GALVANISED (2 X M20 X 80 LG C/W 1 NUT, 6 X M20 X 90 LG C/W 1 NUT)
UNION ALL
SELECT STUD BOLT SET, SANS 1700, GR 8.8, SANS 1700, GR 8, GALVANISED (8 X M24 X 130 LG C/W 1 NUT, 12 X M24 X 150 LG C/W 1 NUT)
UNION ALL
SELECT STUD BOLT SET, ASME B1.1, ASTM A193 B7, ASME B1.1, ASTM A194 GR 2H, GALVANISED (28 x 1 3/4" UN8 x 270 LG C/W 1 NUT)
)
SELECT
SUBSTRING(description, LEN(description) - pos1 - pos2, pos2 - 1)
FROM MyTable
/* REVERSE the string so that searching for the rightmost character/pattern will be equivalent to searching the leftmost character/reversed pattern in the reversed string */
CROSS APPLY (SELECT REVERSE(description)) AS rev(revDescription)
/* Find the rightmost occurrence of LG , by searching the leftmost occurrence of GL in the reserved string */
CROSS APPLY (SELECT patindex(REVERSE(N % % LG% ), revDescription)) AS ca1(pos1)
/* Skipping the L (from LG) and its preceding space (i.e. starting on character 3), find the rightmost space */
CROSS APPLY (SELECT CHARINDEX( , SUBSTRING(revDescription, pos1 + 3, 1000))) AS ca2(pos2)
其他数据库可能更能支持定期的表述,使查询更加容易撰写。 下文是Pogres的例子,我们能够在1行中做到这一点;类似做法也可能与其他数据库合作。
WITH MyTable(description) AS (
SELECT STUD BOLT SET, SANS 1700, GR 8.8, SANS 1700, GR 8, GALVANISED (8 X M24 X 140 LG C/W 1 NUT, 12 X M24 X 170 LG C/W 1 NUT)
UNION ALL
SELECT STUD BOLT SET, SANS 1700, GR 8.8, SANS 1700, GR 8, GALVANISED (2 X M20 X 80 LG C/W 1 NUT, 6 X M20 X 90 LG C/W 1 NUT)
UNION ALL
SELECT STUD BOLT SET, SANS 1700, GR 8.8, SANS 1700, GR 8, GALVANISED (8 X M24 X 130 LG C/W 1 NUT, 12 X M24 X 150 LG C/W 1 NUT)
UNION ALL
SELECT STUD BOLT SET, ASME B1.1, ASTM A193 B7, ASME B1.1, ASTM A194 GR 2H, GALVANISED (28 x 1 3/4" UN8 x 270 LG C/W 1 NUT)
)
SELECT UNNEST(regexp_matches(description, (d+)s*LG(?!LG) ))
FROM MyTable
In this case, we capture the number ((d+)) before LG. (?!LG) is a negative lookahead, which means there cannot be allowed another occurrence of LG to the right of the one we want to capture.
If you were to remove (?!LG) (as well as the UNNEST), you would get all the numbers immediately preceding all the occurrences of LG in 1 array per input string.
