Find volumes by keying in on Units of Measure, but when there's two?

Question

I have a string like below:

194736 BBLS FLUID 3800880 LBS 40/70 361060 LBS

I have used the following to fish out the '3800880' where I used a function to grab the right most word to the left of the 'LBS'. This does a decent enough job about finding the first value to the left of the 'LBS'. How do I go about getting the second value of 'LBS' there? The 361060 too? Either sum them up, or separate is fine

replace([dbo].[StripNonNumerics](dbo.getLastWord((SUBSTRING (fluid_description,0,PATINDEX('%LBS%',fluid_description))))),'.','') as LBSAmount,

Charindex, patindex, etc. How can you key off of 'Multiple' of the same keyword in you string?

Answer 1

Getting data out of unstructured strings is always going to be a problem, the easy solution is don't store data like that.

However to extract both values from the string and sum them, consider parsing the string as Json with openJson. This allows you to treat each "word" as a row and therefore able to utilise window functions to extract the required data:

select Sum([value])
from (
  select '194736 BBLS FLUID 3800880 LBS 40/70 361060 LBS' )d(MyBadData)
  cross apply (  
    select case when lead([value]) over(order by j.[key]) ='LBS' 
      then Try_Convert(int, [value]) 
    end [value]
  from OpenJson(Concat('["',replace(MyBadData,' ', '","'),'"]')) j
)x;

Result: 4161940