Strip text and extract numeric value from string using TSQL
Posted on: September 18, 2013 /
Categories: SQL Server
Today I had an issue that someone changed format of a string pattern our SQL was expecting. I came across a function written by George Mastros that addressed my SQL needs. Long story short we have a string with text then a number and some more text. What we need is to extract the numeric value from this string. The original code someone wrote at work had lots of replace this and replace that plus substring brute force approach, but if the string now did not include bracket then we had a problem since replace was looking for it. So I just marginally altered George’s code to suit my needs to fish out positive numbers from a string that contains only on multi digit number with a decimal place:
left(SubString(stringfield, PatIndex(‘%[0-9.]%’, stringfield), 8000), PatIndex(‘%[^0-9.]%’, SubString(stringfield, PatIndex(‘%[0-9.]%’, stringfield), 8000) + ‘X’)-1)
This now will extract numbers such 3.00 or 45 from a string. Of course this still is limited as the pattern can’t include more than one number but in my case that is sufficient.