SQL: select the last values before a space in a string

I have a set of strings like this:

CAP BCP0018 36
MFP ACZZ1BD 265
LZP FEI-12 3

I need to extract only the last values from the right and before the space, like:

36
265
3

how will the select statement look like? I tried using the below statement, but it did not work.

select CHARINDEX(myField, ' ', -1)
FROM myTable;

Answer

Perhaps the simplest method in SQL Server is:

select t.*, v.value
from t cross apply
     (select top (1) value
      from string_split(t.col, ' ') 
      where t.col like concat('% ', val)
     ) v;

This is perhaps not the most performant method. You probably would use:

select right(t.col, charindex(' ', reverse(t.col)) - 1)

Note: If there are no spaces, then to prevent an error:

select right(t.col, charindex(' ', reverse(t.col) + ' ') - 1)