Parse data in a column to create 2 other columns - substring

This is an MSSQL query question.

I'm trying to use the substring function in a SQL query to get parts of a column to create other columns, but is there a way to look for characters instead of telling it where to start and how many characters to take? In the below data, I always want to grab the numbers that are between the ' '. I then want to put them in columns called "Write" and "Prev".

Input Data: Write '8' to '/FOUNDRY::[Foundry_Muller]F26:30'. Previous value was '9.0'

Results: Write = 8 Prev = 9.0

UPDATE: I've been refining this query and now have a problem. On the "Prev2" substring if I have a space after "'was" I get "Invalid length parameter passed to the left or substring function" If I remove the space it runs, but I get the wrong answer. I would appreciate another set of eyes on this.

SELECT

    [MessageText],
    [Location],
    [UserID],
    [UserFullName],
    CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, [TimeStmp]), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS RecordTime, 
    substring(MessageText, (patindex('%Write ''%', MessageText)+7), patindex('%'' to ''%', MessageText)-(patindex('%Write ''%', MessageText)+7)) as Writen,
    substring(MessageText, (patindex('%Previous value was ''%', MessageText)+20),len(MessageText)-(patindex('%Previous value was ''%', MessageText)+21)) as Prev,
    SUBSTRING(MessageText, CHARINDEX('[', MessageText) + 1, CHARINDEX(']', MessageText) - CHARINDEX('[', MessageText) - 1) AS PLC,
    SUBSTRING(MessageText, CHARINDEX(']', MessageText) + 1, CHARINDEX('''', MessageText, CHARINDEX(']', MessageText)) - CHARINDEX(']', MessageText) - 1) AS TAG,
        CASE
        WHEN CHARINDEX('was ''', [MessageText]) > 0 THEN SUBSTRING([MessageText], CHARINDEX('was ''', [MessageText]) + 20, CHARINDEX('''.', [MessageText]) - CHARINDEX('was ''', [MessageText]) - 20)
        ELSE NULL
    END AS Prev2

FROM
   [DiagLog].[dbo].[Diag_Table]

Solution

This is mighty fugly but it works (give me a proper regex any day). The 'with' statement is a common table expression and is used here to just set up test data, like a temp table (a great way to set up data for examples here). The meat is the query below that.

Select the substring starting at the pattern "Write '" + 7 to get you at the first digit. The length to return is that number from the starting point of the pattern of "' to'". So, this allows for a variable length "Write" value as long as the format of the string stays the same.

with tbl(str) as (
select 'Input Data: Write ''8989'' to ''/FOUNDRY::[Foundry_Muller]F26:30''. Previous value was ''229.0'''
)
select substring(str, (patindex('%Write ''%', str)+7), patindex('%'' to ''%', str)-(patindex('%Write ''%', str)+7)) as write_val,
  substring(str, (patindex('%Previous value was ''%', str)+20),len(str)-(patindex('%Previous value was ''%', str)+20)) as prev_val
from tbl;