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

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

New_Value

18.8
13.8 is my tax rate.
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!

score 0 · Answer 1 · answered Sep 30 '18 at 04:14

If there is always a Percent sign after the numerics, you might try the following, and then convert or cast the result into decimal.

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

From patindex

score 0 · Accepted Answer · answered Sep 30 '18 at 04:21

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

Thanks! This worked but I have some test scenarios where it did not work. https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=e1393a1b7f77fe3dd64de3384cf1a039 — MRBasun, Sep 30 '18 at 16:27
@NepaliBalak: Well, I clearly wrote, that this needs that there's only one number in the string... — sticky bit, Sep 30 '18 at 16:49

score 0 · Answer 3 · answered Sep 30 '18 at 04:31

This will give you just the number before a % sign as a decimal. You'd need to validate that % exists in the predicate.

Select CAST(TRIM(SUBSTRING(Column_Desc,LOCATE('%',Column_Desc)-4,LOCATE('%',Column_Desc)-1)) AS DECIMAL(3,1))

In your question you show a result as: 13.8 is my tax rate. If that's one field it has to be a text field of some sort. Then you are just better of replacing the % with a ''

score 0 · Answer 4 · answered Sep 30 '18 at 11:56

Your in your examples the numbers are all exactly 4 characters long. If this is always true, then the simplest method is:

select Column_Desc,
       convert(decimal(5, 1),
               left(stuff(Column_Desc, 1,
                          patindex('%[0-9]%', Column_Desc) - 1, ''), 4)
              ) as new_value

Here is a db<>fiddle.

SQL Server does not have very good string processing functions. So, being able to make such assumptions about the string that you are looking for simplifies the code.

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

4 Answers4