Detecting cells in column that cause error in SQL

问题:

Assuming that we are trying to alter the type of a column in a SQL table, say from varchar to float, using: ALTER TABLE <mytable. ALTER COLUMN <mycolumn> FLOAT. However, we get the error Error to convert datatype varchar to float.

Is it possible to narrow down the cells in the column that are causing this problem?

Thanks,

回答1:

You can use the ISNUMERIC function:

select * from table where isnumeric(mycolumn) = 0

If you allow NULL values in your column, you'll also need to add a check for NULLs since ISNUMERIC(NULL) evaluates to 0 as well

select * from table where isnumeric(mycolumn) = 0 or mycolumn is not null

回答2:

I have encounter the same issue while writing ETL procedure. moving staging data into actual core table and we had all columns on staging table a NVARCHAR. there could be a numeric value which is either scientific format (like very large float values in Excel cell) or it has one of this special CHAR in it. ISNUMERIC function evaluates this char as True when it is appear as whole value. for example

    SELECT ISUMERIC('$'), ISNUMERIC('.')

so just check if any of cell in that column has such values.

    '$'
    '-'
    '+'
    ','
    '.'

if you find that cell has one of above then just exclude such data in your query. if you find that you have data in scientific format like "1.2408E+12" then ISNUMERIC will be still evaluate it as TRUE but straight insert will fail so convert in appropriate numeric format.

    DECLARE @t NUMERIC(28,10)
    SELECT @t=CONVERT(NUMERIC(28,10),CONVERT(FLOAT,'1.2408E+12'))
    SELECT @t

回答3:

Dirty, but effective. This removes all characters found in floats (#s and decimal - I'm US-centric). The result you get from the query are items that would need to be reviewed to determine what should be done (ie the cells causing you problems).

SELECT
    *
FROM (
    SELECT
            TableId
        ,   REPLACE(
            REPLACE(
            REPLACE(
            REPLACE(
            REPLACE(
            REPLACE(
            REPLACE(
            REPLACE(
            REPLACE(
            REPLACE(
            REPLACE(
                ISNULL(Col1,'')
            ,'0','')
            ,'1','')
            ,'2','')
            ,'3','')
            ,'4','')
            ,'5','')
            ,'6','')
            ,'7','')
            ,'8','')
            ,'9','')
            ,'.','') [FilteredCol1]
    FROM Table
) a
WHERE len(a.[FilteredCol1])>0

回答4:

Select any records where the varchar value contains any non-numeric characters

SELECT col
FROM   tab
WHERE  col LIKE '%[^0-9.]%'

and any rows that might have more than one period:

SELECT col
FROM   tab
WHERE  col LIKE '%.%.%'