Parse data in a column to create 2 other columns

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

CodePudding user response：

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;