CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Gowtham Ramamoorthy
Gowtham Ramamoorthy

Reputation: 896

How to use Isnumeric in dynamic sql?

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

Upvotes: 0

Views: 500

Answers (3)

Tom H
Tom H

Reputation: 47402

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.

Upvotes: 1

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23098

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.

Upvotes: 1

TheGuyThatDoesn'tKnowMuch
TheGuyThatDoesn'tKnowMuch

Reputation: 1250

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!

Upvotes: 0

Related Questions