none
CASE WHEN isnumeric(ColValue) THEN ... ELSE ... END RRS feed

  • Question

  • From what I have seen so far you cannot use as statement such as...

    CASE WHEN isnumeric(ColVal) THEN convert(int, ColVal) ELSE ColVal END as ColVal

    as an expression as for a column value because convert(int, ColVal) is evaluated regardless of how the CASE WHEN expression evaluates.

    Is this correct? Is there another way to test whether a column value is numeric before attempting top convert it to a numeric value?

    Why do I want to do this? Because the dataset is returned to an Excel worksheet and I have been using Condition Formatting that requires the values to be numeric. One particular site that needs to be queried returns non numeric values so the Conditional Formatting won't work, but I would like to retain it for the other sites. The only solution so far seems to be to have a slightly different T-SQL script for the problem site.


    R Campbell


    Monday, March 27, 2017 11:35 PM

Answers

  • CASE WHEN try_cast(ColVal as int)  is not null THEN convert(int, ColVal) ELSE null END as ColVal

    The column should be int  column. You don't mix data type in a column of your table.


    Tuesday, March 28, 2017 12:58 AM
    Moderator

All replies

  • Sounds to me like what you need is an ETL tool.  How are you getting the Excel file into the database?
    Tuesday, March 28, 2017 12:13 AM
  • Sounds to me like what you need is an ETL tool.  How are you getting the Excel file into the database?

    It's a stored procedure called from Excel an VBA macro via a button on the target worksheet. The problem for me is that when Excel detects text in a column it formats the cells as text, which stops the Conditional Formatting from working. If the data is actually text, all is lost anyway (as far as Conditional Formatting is concerned), but even if the data is numeric, Excel still formats it as text.

    I think Excel makes this decision based on the first 8 rows. I have used SSIS for many years and have seen similar problems there. when Excel is the destination. Also these are SQLExpress databases so I don't think that SSIS is available.

    The Excel macro already does a lot of formatting, I might see if I can fins a way to remove the text formatting from the column in question. I haven't managed to that yet.


    R Campbell

    Tuesday, March 28, 2017 12:37 AM
  • So you're trying to output from a table to Excel? Converting a column to int won't tell Excel to format as an int. Your statement is invalid in SQL terms and I wouldn't say it's because it's attempting to evaluation the convert but because you are trying to convert valid int but still assigning invalid ints to the same column. SQL won't like that, a demo... the first query won't work because we're attempting to assign 'a' to a column that SQL has determined will be int but the second query runs fine

    select case when isnumeric('a') = 1 then convert(int, 'a') else 'a' end
    select case when isnumeric('a') = 1 then convert(int, 'a') else '1' end

    As another aside, isnumeric isn't strictly accurate, it will identify '.' as "isnumeric"

    For your situation, sounds like you may need two scripts. If you output a set of data where you can be 100% certain all ColVal values are integer, does Excel work?

    Tuesday, March 28, 2017 12:40 AM
  • CASE WHEN try_cast(ColVal as int)  is not null THEN convert(int, ColVal) ELSE null END as ColVal

    The column should be int  column. You don't mix data type in a column of your table.


    Tuesday, March 28, 2017 12:58 AM
    Moderator
  • CASE WHEN try_cast(ColVal as int)  is not null THEN convert(int, ColVal) ELSE null END as ColVal

    The column should be int  column. You don't mix data type in a column of your table.


    The try_cast() function works in SQL 2012 but not in earlier versions. Even in SQL 2012 Intellisense reports it as not being a built in function. Anyway, it looks as though a long standing issue has been addressed in SQL 2012.

    I did find a solution in Excel VBA but it was a bit ugly. I had to allow the data to be returned as text. Excel proceeded to format the cells as text, even if the all values were numeric. In VBA code I then selected the cells, used Selection.ClearFormats  and finally iterated through the cells setting Cell.Value=Val(Cell.Value).


    R Campbell

    Tuesday, March 28, 2017 3:26 AM