How to use Isnumeric in dynamic sql?

Question

I need to know how to fetch the numerical values from the outputformula column which is in varchar datatype.

I use this outputformula column in a dynamic sql query to perform mathematical calculations.

I have the below code for the table and the select query to fetch the values.

Create table #table1
(
IDNUM  int,
outputformula  varchar(60)
)

insert into #table1
values 
(1, '67349.000 +78343.000'),
(2, 'a +78343.000'),
(3, '45783.00+b'),
(4, '6152.000 +1524.000'),
(5, 'cda +7151.000'),
(6, '67349.000 +78343.000')

    Select outputformula from #table1 where ISNUMERIC(outputformula) =1

When I execute the code I get no values is there a way to get the output of only the numerical values apart from the alphabets in the column....

I should get the output column as

 67349.000 +78343.000
 6152.000 +1524.000
 67349.000 +78343.000

Looks like you will have to look up a splitter to split the strings (using space as a delimiter), then use the ISNUMERIC function on the return. — TT., Feb 23 '16 at 19:21
How are you getting `NULL`s? Your query returns no rows at all. Can you edit your question with your expected results? — Tom H, Feb 23 '16 at 19:23
You might be better off writing a UDF rather than trying to force a way to use ISNUMERIC. — Tab Alleman, Feb 23 '16 at 19:37
ISNUMERIC is a horribly named function. It should be something more like IsTheValueSomethingThatCanBeConvertedToAnyDatatypeThatIsRoughlyNumberOriented. That of course is entirely too long. but IsNumeric will return a lot of false positives. 1e2 or $ both evaluate to true. — Sean Lange, Feb 23 '16 at 19:39
Hi guys.. thanks for the comments... I need the output to be used for doing math in dynamic SQL... hence i have just added few edits..... — Gowtham Ramamoorthy, Feb 23 '16 at 21:03

Tom H · Accepted Answer · 2016-02-23T21:13:40.250

Unfortunately, ISNUMERIC does not evaluate formulae. You can try to write your own simple parser in a SQL function, but functions don't allow for any kind of error handling, so when you try to evaluate one of the non-numeric equations you'll run into issues.

Your best bet is likely to use whatever you're using to evaluate these equations to also perform this task - I assume that's in some sort of front end.

Since you aren't using a front end, here's the best that I can come up with for a straight SQL approach. First, create a stored procedure that can evaluate numeric only equations. This would be better as a function, but unfortunately functions don't allow TRY..CATCH blocks, which is crucial for handling non-numeric equations. Here's an example of such a stored procedure:

CREATE PROCEDURE dbo.Check_Dynamic_Formula
    @formula VARCHAR(60),
    @result DECIMAL(10, 4) OUTPUT
AS
BEGIN
    DECLARE
        @sql NVARCHAR(100)

    BEGIN TRY
        SELECT @sql = 'SELECT @inner_result = ' + @formula

        EXEC sp_executesql @sql, N'@inner_result DECIMAL(10, 4) OUTPUT', @inner_result = @result OUTPUT
    END TRY
    BEGIN CATCH
        SELECT @result = NULL
    END CATCH
END

Once you have that you can set up a CURSOR to go through your table one row at a time (which is why a scalar function would have been much better since you could then avoid a CURSOR). Check the output variable for each row in your table. If it's a NULL then the stored procedure couldn't evaluate it.

Some important caveats...

There may be some instances where the evaluation becomes a numeric unintentionally - see @Sean Lange's comment to your question.

IMPORTANT This is highly susceptible to injection. I would not run this against any data that was available for a user to generate. For example, if you have users entering the formulae then they could make a SQL injection attack.

Finally, if any other error occurs in the TRY..CATCH block, it will make it appear as if the row was a non-numeric. We're counting on the code failing to prove that it's non-numeric, which is a brittle approach. Any error could give you a false negative.

Hi Tom... thanks for the comments... Unfortunately im using the entrire eveluation inside a stored procedure using dynamicsql... Is there any alternate way you can suggest for this ? — Gowtham Ramamoorthy, Feb 23 '16 at 20:49

score 1 · Answer 2 · edited May 23 '17 at 11:59

If all your strings are in the format {token1}blank{token2} one way is to define a Split function in your database and use the following code:

Select T.*, T1.StringValue, ISNUMERIC(T1.StringValue), T2.StringValue, ISNUMERIC(T2.StringValue), ISNUMERIC(T1.StringValue) * ISNUMERIC(T2.StringValue) AS IsNumeric
from #table1 T
cross apply dbo.Split(T.outputformula, ' ') T1 
cross apply dbo.Split(T.outputformula, ' ') T2 
where T1.Ordinal = 1 and T2.Ordinal = 2

However, you should take into consideration that SQL ISNUMERIC function has some limitations and provides some false positives.

One way to have more specific numeric checking is to use TRY_CONVERT function.

score 0 · Answer 3 · edited May 23 '17 at 12:31

0

ISNUMERIC will only ever return a 1 or a 0. In this case, you are asking the report to return anything in the outputformula column that is a number, but since you declared the field as a VARCHAR(60) this will always return 0 results.

Check out this other post for a potential answer to your problem.

Query to get only numbers from a string

Hope this helps!

edited May 23 '17 at 12:31

Community

1
1

answered Feb 23 '16 at 19:36

TheGuyThatDoesn'tKnowMuch

1,240
1
17
28

How to use Isnumeric in dynamic sql?

3 Answers3