Finding out just the numeric part from string and storing it as decimal datatype

Question

I tried using patindex to find the starting of the number value in a string. My goal is to just extract the numeric part from the string excluding the %.

With the below query, here are my results :

Select Column_Desc, Replace(substring([Column_Desc], PatIndex('%[0-9]%', [Column_Desc]), len([Column_Desc])),'%','') as New_Value

Column_Desc

1. My tax rate is 18.8% **.
2. 13.8% is my tax rate.
3. My tax rate 15.9% What is yours ?

New_Value

1. 18.8
2. 13.8 is my tax rate.
3. 15.9 What is yours?

So, the result(New_Value) should be 18.8, 13.8 and 15.9 with a datatype of decimal. I am unable to get it to work. Please advise. Thank you!

Answer 1

If there can be only one such number in a string and if it always starts with a digit (i.e. there are no values omitting the 0 before the decimal like '.75') and ends with a digit, you can find the first digit by applying patindex() to the string (as you already do) and the last digit by applying patindex() to the reverse() of the string.

SELECT convert(decimal(3, 1),
               substring(column_desc,
                         patindex('%[0-9]%',
                                  column_desc),
                         len(column_desc)
                         - patindex('%[0-9]%',
                                    column_desc)
                         - patindex('%[0-9]%',
                                    reverse(column_desc))
                         + 2)) new_value
FROM elbat;

db<>fiddle