Something like this looks to be working:

SELECT  RIGHT(SUBSTRING(string_nonspace, y.start, PATINDEX('%[^0-9]%', STUFF(string_nonspace, 1, start + 1, '')) + 1), 4)
FROM    (
    VALUES  (N'Brian Larry, Hays Hill XX, ID007370, Option code - 0123 4567, AAA - XX')
    ,   (N'ID007366 - Dave Jones - XX - Option Code 0121 9999')
    ,   (N'ID00 7120  Brian Smith XX  Branded company')
    ,   (N'ID07113 Gary Barnes  - LLL 0123 9111 AAA LLL')
    ,   (N'ID00 7120  Charles Old XX   Recall operation')
    ,   (N'ID0007439 - Kerry Hill - Maidstone XX - Option Code 0124 234 BBB XX')
    ,   (N'ID006817 Paul George Jackson 1234 8464 AAA Recall operation')
) t (col1)
CROSS APPLY (
        SELECT  replace(col1, ' ', '') AS string_nonspace
    ) x
CROSS APPLY (
        SELECT  PATINDEX('%ID[0-9]%', string_nonspace) AS start
    ) y

Output:

I remove spaces and then find starting position by PATINDEX('%ID[0-9]%' . Then i clip the string so ID-part is removed, and then search for first non-number value. When that done, you can do a SUBSTRING between start and the first non-number value. Finally, RIGHT(..., 4) gets the last four characters.

Of course this code will fail on many malformed strings, but them the breaks, the in-data kinda "blowers"

Although SQL is not that friendly when it comes to string extractions, you can Try this :

SELECT SUBSTRING([column], LEN([column]) - 4, 4)
FROM [table];

This query will first find the length of the string in the [column] field using the LEN() function. Then, it will use the SUBSTRING() function to extract the last 4 characters of the string, starting at the length of the string minus 4. You can also use the following query to extract the last 4 digits of the ID00000 number, even if the ID number varies in length:

SELECT SUBSTRING([column], PATINDEX('%ID0%-%[0-9][0-9][0-9][0-9]%', [column]) + 6, 4)
FROM [table];

This query will first use the PATINDEX() function to find the position of the first occurrence of the string ID0%- in the [column] field. Then, it will use the SUBSTRING() function to extract the last 4 characters of the string, starting at the position of the ID0%- string plus 6.

There is no solution that guaranties you will have a correct result because you violate the 1FN which is the most evil thing to do...

Just have a look if you have a new row like this one :

insert into Sample (SampleString)
values ('Brian Larry, LUCID007700880099 Hays Hill XX, ID007370, Option code - 0123 4567, AAA - XX')

Or this one :

insert into Sample (SampleString)
values ('Brian LUCID0AZ, Hays Hill XX, ID007370, Option code - 0123 4567, AAA - XX')