Conditionally branching in SQL based on the type of a variable.

Question

Hi everyone.

I'm selecting a value out of a table that can either be an integer or a nvarchar. It's stored as nvarchar. I want to conditionally call a function that will convert this value if it is an integer (that is, if it can be converted into an integer), otherwise I want to select the nvarchar with no conversion.

This is hitting a SQL Server 2005 database.

select case
    when T.Value (is integer) then SomeConversionFunction(T.Value)
    else T.Value
end as SomeAlias

from SomeTable T

Note that it is the "(is integer)" part that I'm having trouble with. Thanks in advance.

UPDATE

Check the comment on Ian's answer. It explains the why and the what a little better. Thanks to everyone for their thoughts.

Answer 1

You can't have a column that is sometimes an integer and sometimes a string. Return the string and check it using int.TryParse() in the client code.

Answer 2

ISNUMERIC. However, this accepts +, - and decimals so more work is needed.

However, you can't have the columns as both datatypes in one go: you'll need 2 columns.

I'd suggest that you deal with this in your client or use an ISNUMERIC replacement

Answer 3

IsNumeric will get you part of the way there. You can then add some further code to check whether it is an integer

for example:

select top 10 
    case 
        when isnumeric(mycolumn) = 1 then 
            case 
                when convert(int, mycolumn) = mycolumn then
                    'integer'
                else
                    'number but not an integer'
            end
        else 
            'not a number'
    end 
from mytable

Answer 4

 select case
     when ISNUMERIC(T.Value) then T.Value 
     else SomeConversionFunction(T.Value)
 end as SomeAlias

Also, have you considered using the sql_variant data type?

Answer 5

To clarify some other answers, your SQL statement can't return different data types in one column (it looks like the other answers are saying you can't store different data types in one column - yours are all strign represenations).

Therefore, if you use ISNUMERIC or another function, the value will be cast as a string in the table that is returned anyway if there are other strigns being selected.

If you are selecting only one value then it could return a string or a number, however your front end code will need to be able to return the different data types.

Answer 6

The result set can only have one type associated with it for each column, you will get an error if the first row converts to an integer and there are strings that follow:

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value 'word' to data type int.

try this to see:

create table  testing
(
strangevalue   nvarchar(10)
)

insert into testing values (1)
insert into testing values ('word')
select * from  testing

select
    case
        when ISNUMERIC(strangevalue)=1 THEN CONVERT(int,strangevalue)
        ELSE strangevalue
     END
FROM testing

best bet is to return two columns:

select
    case
        when ISNUMERIC(strangevalue)=1 THEN CONVERT(int,strangevalue)
        ELSE NULL
     END AS StrangvalueINT
    ,case
        when ISNUMERIC(strangevalue)=1 THEN NULL
        ELSE strangevalue
     END AS StrangvalueString
FROM testing

or your application can test for numeric and do your special processing.

Answer 7

Just to add to some of the other comments about not being able to return different data types in the same column... Database columns should know what datatype they are holding. If they don't then that should be a BIG red flag that you have a design problem somewhere, which almost guarantees future headaches (like this one).