Check if a result is an integer/varchar/etc. during SQL execution?

Question

I'm using MySQL and MSSQL on different servers so I need to learn how to do this both ways. I have a column that has the type of varchar. Generally it will be filled with integers and floats, but every now and then it will be filled with text saying "See Notes" or something to that effect. I have a query builder so the user can compare the results against numbers. I can convert the user's input from integers to floats and SQL handles that just fine when testing against integers and floats, but the problem comes whenever it reaches a varchar.

To complicate matters further, the user inputs is in a query builder so I wouldn't be able to just run 2 separate queries to break things up. So the first thing I need to know is how SQL handles queries. In PHP if you have an if statement, and the first condition fails, it will never go on to the second condition. For example: if($_POST && $_POST['result']) the code will not try to read the result of $_POST because $_POST doesn't exist.

If it works the same way, then I need some way to test if the result is an integer. If it is, then it can continue running the script. But I don't know how I can do that either. Something like this:

SELECT *
FROM my_table
WHERE ISNUMERIC(result) = 1 AND result > 4.5

So that if result contained a varchar, it would terminate before reaching the result > 4.5 so I wouldn't break my statement and get errors.

Thanks.

Can you just add another column that will store your float or integer? — Kermit, Jan 10 '13 at 23:59
I'd have another look at the table design. Generally, if you need to store numbers most of the time then store it in a number field. If you need to store text then store it in a text field. If you need to store both, consider using 2 fields and making them nullable (then the user only needs to enter 1 field) — Greg, Jan 11 '13 at 00:01
you could have a text, int and float fields, with a special column that only tells you which field is filled to avoid checking null values — Frederik.L, Jan 11 '13 at 00:04
I would echo @Greg comment, but if you already have a column that is has mixed data content and you can't change your schema, then why do you really care at the MySQL/MSSQL level whether the item is a number or a string. Could you not just deal with the number vs. string issue in your application? — Mike Brant, Jan 11 '13 at 00:05
relevant to part of an answer http://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated — goat, Jan 11 '13 at 00:53
@Greg - That is a good idea. I implemented that into my table structure. Thanks. Please post that as an answer so I can accept it. — Aust, Jan 11 '13 at 18:21

score 1 · Answer 1 · edited Feb 01 '13 at 15:23

1

If you are using SQL Server, your query above works fine. Here is an alternative for you to try though:

SELECT *
FROM my_table 
WHERE result > 
  CASE WHEN ISNUMERIC(result) = 1
  THEN '5'
  ELSE result
  END

Here is the SQL Fiddle for both.

If you are using MySQL, then this seems to work:

SELECT *
FROM my_table 
WHERE result > 5

And the SQL Fiddle.

edited Feb 01 '13 at 15:23

wickedone

542
1
6
18

answered Jan 11 '13 at 00:31

sgeddes

62,311
6
61
83

mysql doesn't have a native ISNUMERIC function. – goat Jan 11 '13 at 00:45
I knew that, the OP tagged SQL Server as well. I updated my answer to include both. Thanks – sgeddes Jan 11 '13 at 00:46

score 1 · Accepted Answer · answered Jan 11 '13 at 20:17

I'd have another look at the table design. Generally, if you need to store numbers most of the time then store it in a number field. If you need to store text then store it in a text field. If you need to store both, consider using 2 fields and making them nullable (then the user only needs to enter 1 field)

score 0 · Answer 3 · answered Jan 11 '13 at 00:08

I've found it very useful to have a function which, given a text input, will return an INT, or NULL if the value cannot be parsed. You could then simply query

WHERE dbo.ParseInteger(result) >= 3

In my case, I'm only interested in integers, but I'm sure you can extend this to accommodate floats. This may be more elaborate than you need; if you don't care about exponents, for example, you can throw out 70%. I'm too rusty on MySQL to offer a translation. Finally, note that I'm assuming English-style numbers, you may have different group and decimal separators.

CREATE FUNCTION dbo.ParseInteger(@Input VARCHAR(100)) RETURNS BIGINT WITH SCHEMABINDING
AS BEGIN
    SET @Input = dbo.Trim(@Input)  -- If you're not worried about linebreaks or other odd chars, LTRIM(RTRIM(@Input)) will be fine
    IF ISNUMERIC(@Input) = 0 RETURN NULL
    IF @Input IN ('.', '+', '-', ',') RETURN NULL
    SET @Input = REPLACE(@Input, ',', '')  -- Strip commas

    DECLARE @DecimalPos INT = CHARINDEX('.', @Input)
    DECLARE @ExpPos     INT = CHARINDEX('E', @Input)
    DECLARE @IntValue   BIGINT
    IF @DecimalPos = 0 AND @ExpPos = 0
        BEGIN
        -- There's no decimal and no exponent, so we can easily cast this bog-standard integer
        SET @IntValue = CAST(@Input AS BIGINT)
        END
    ELSE IF @DecimalPos > 0 AND @ExpPos = 0
        BEGIN
        -- There's a decimal point but no exponent; we can cast the integer part, and then nudge it if necessary to round off the tenths place
        SET @IntValue = CAST(SUBSTRING(@Input, 1, @DecimalPos - 1) AS BIGINT)
        IF SUBSTRING(@Input, @DecimalPos + 1, 1) BETWEEN '5' AND '9'
            IF @IntValue < 0
                SET @IntValue -= 1
            ELSE
                SET @IntValue += 1
        END
    ELSE
        BEGIN
        -- There's an exponent, and probably a decimal; this will be relatively complicated
        IF @DecimalPos = 0
            BEGIN
            -- There's no decimal; insert one, just so we have consistency downstream
            SET @Input      = LEFT(@Input, @ExpPos - 1) + '.0E' + RIGHT(@Input, LEN(@Input) - @ExpPos)
            SET @DecimalPos = @ExpPos
            SET @ExpPos    += 2
            END

        DECLARE @Magnitude INT = CASE WHEN LEFT(@Input, 1) = '-' THEN @DecimalPos - 2 ELSE @DecimalPos - 1 END  -- For normalized scientific notation, this will always be one, but we can't expect that
        DECLARE @Exponent  INT = CAST(RIGHT(@Input, LEN(@Input) - @ExpPos) AS INT)
        IF @Exponent > 18 RETURN NULL  -- BIGINT can handle values up to 2^63, or 9.2E18

        SET @Input = REPLACE(SUBSTRING(@Input, 1, @ExpPos - 1), '.', '')

        DECLARE @MagAdjustment INT = @Magnitude + @Exponent - CASE WHEN LEFT(@Input, 1) = '-' THEN LEN(@Input) - 1 ELSE LEN(@Input) END

        IF @MagAdjustment > 0
            BEGIN
            SET @Input += REPLICATE('0', @MagAdjustment)
            END
        ELSE IF @MagAdjustment < 0
            BEGIN
            WHILE -@MagAdjustment > @Magnitude AND LEN(@Input) > 1
                BEGIN
                SET @MagAdjustment += 1
                SET @Input          = SUBSTRING(@Input, 1, LEN(@Input) - 1)
                END

                IF -@MagAdjustment > @Magnitude SET @Input = '0'
            ELSE IF -@MagAdjustment = @Magnitude SET @Input = CASE WHEN LEFT(@Input, 1) BETWEEN '5' AND '9' THEN '1' WHEN LEFT(@Input, 2) BETWEEN '-5' AND '-9' THEN '-1' ELSE '0' END
            ELSE                                 SET @Input = SUBSTRING(@Input, 1, CASE WHEN LEFT(@Input, 1) = '-' THEN 1 ELSE 0 END + LEN(@Input) + @MagAdjustment)
            END

        SET @IntValue = CAST(@Input AS BIGINT)
        END

    RETURN @IntValue
END

Check if a result is an integer/varchar/etc. during SQL execution?

3 Answers3