Getting ranged data value from varchar(500)

Question

Looking for some ideas.

I'm running SQL Server 2014. I have a varchar(500) column that has a data value that I need to extract from each row, however, it is not always in the same position on the row (sometimes off by 1 or 2 positions) so I'm having difficulty with the substring:

substring(Failure_Reason, charindex('Pressure received:', Failure_Reason + 181, 5) as 'Test'

Using this line of code, most of the time I get the correct data value, however sometimes I get a value like '67.T'

The varchar column's content is:

Settings received from Creep tester are outside expected values for pouch 3.            Pressure Duration received : 0. This value should be 32 +/- 2.          Pressure received : 198.5. This value should be between 202.77 and 197.24..

The verbage is always the same but the data values would be different

I'm just trying to parse out the value after the "Pressure Received:" but as it may be a position or two off in certain rows, I sometimes pull in the "." or the "T", is there a way to catch the value between the "Pressure Received:" and the "This", so I only get the numeric value?

Thanks!

Answer 1

The following should show you how to approach this:

1. Start position is location of string "Pressure received :" + the length of that string + 1
2. End position is the location of the string "This" - 1, with the search for this string starting at the position calculated in step 1
3. Get the substring between the two values calculated in the previous step
4. Trim the substring
5. If the last char in the substring is ".", substring it again to a length of 1 less than the current length

Answer 2

Here is a solution:

 --==== Setup some test data
Declare @testData Table (Failure_Reason varchar(500));
 Insert Into @testData (Failure_Reason)
 Values ('Settings received from Creep tester are outside expected values for pouch 3.            Pressure Duration received : 0. This value should be 32 +/- 2.          Pressure received : 198.5. This value should be between 202.77 and 197.24..')
      , ('Settings received from Creep tester are outside expected values for pouch 3.            Pressure Duration received : 0. This value should be 32 +/- 2.          Pressure received : 98.5. This value should be between 202.77 and 197.24..')
      , ('Settings received from Creep tester are outside expected values for pouch 3.            Pressure Duration received : 0. This value should be 32 +/- 2.          Pressure received : 8.5. This value should be between 202.77 and 197.24..');

 --==== Solution
 Select *
      , PressureReceived = substring(td.Failure_Reason, p1.pos, p2.pos - p1.pos - 1)
   From @testData                                                                         As td
  Cross Apply (Values (charindex('Pressure received :', td.Failure_Reason, 1) + 20))      As p1(pos)
  Cross Apply (Values (charindex('. This', td.Failure_Reason, p1.pos) + 1))               As p2(pos);