0

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
Improve this question
5
  • 1
    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. Commented Feb 23, 2016 at 19:21
  • How are you getting NULLs? Your query returns no rows at all. Can you edit your question with your expected results? Commented Feb 23, 2016 at 19:23
  • You might be better off writing a UDF rather than trying to force a way to use ISNUMERIC. Commented Feb 23, 2016 at 19:37
  • 3
    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. Commented Feb 23, 2016 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..... Commented Feb 23, 2016 at 21:03

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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 ?
1

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.

Improve this answer

Comments

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!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.