Need Help creating a new variable containing data from another variable's string using RIGHT/LEFT and CHARINDEX

Question

I would really apreciate it if someone could help me. I want to create a new variable containing the second number in the Investment_description variable.

Below is a small example of the data in the variable investment_description. I'm having a hard time formatting it using the RIGHT and LEFT functions because the first number changes in size. My end goal is to have code that will format it accuratly to get the exact number in a new column.

Investment_Description 
4 Matching notifications. 11 inserts/Updates.
4000 Matching notifications. 12 inserts/Updates.
32 Matching notifications. 12 inserts/Updates.
171 Matching notifications. 1 inserts/Updates.

Using the example above I want the end result of the new variable to look like this

New_Variable
11
12
12
1

Below this is a rough idea of the code I thought would originally work. But I soon found that I would not be able to use the LEFT and RIGHT functions since the length of the first number in Investment_Description frequently changed in length.

SELECT 

   NH.Investment_Description,
   RIGHT(NH.Investment_Description, CHARINDEX('ations.', NH.Investment_Description)-1) AS New_Variable

FROM Notification_main NM
INNER JOIN Notification_hub NH ON NM.Notification_id = NH.Notification_id;

Answer 1

String operations are not well supported in SQL Server, but you can use:

select  left(v.val1, charindex(' ', v.val1) - 1)
from (values ('32 Matching notifications. 12 inserts/Updates.')
     ) t(Investment_Description) cross apply
     (values (stuff(t.Investment_Description, 1, charindex('. ', t.Investment_Description) + 1, ''))
     ) v(val1);

Here is a db<>fiddle.