Detecting cells in column that cause error in SQL

Question

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,

Derek · Answer 1 · 2013-10-01T16:06:58.133

3

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

edited Oct 01 '13 at 16:06

answered Oct 01 '13 at 13:38

Derek

21,828
7
53
61

Thanks for your answer. But since my column is varchar, your query returns my entire column! – Mayou Oct 01 '13 at 13:40
1

It should only return those rows where the column can not convert successfully to a numerical value. Try this: select isnumeric('0'), which should return 1 despite being a varchar. – Derek Oct 01 '13 at 13:41
It is still returning my entire column.. It is quite odd since my column is essentially all numerical values. I don't understand why SQL wuld have a hard time converting it to float. – Mayou Oct 01 '13 at 13:43
Can you use www.sqlfiddle.com to give some sample data (actual data) that is failing to convert properly? – Derek Oct 01 '13 at 13:45
I have never used sqlfiddle to upload real data, but I will see what I can do to give you a snapshot. – Mayou Oct 01 '13 at 13:47
I just tried using sqlfiddle.com, but my real data contains over 1 million rows. Since I don't know which rows are causing the problem, I can't really replicate the data in sqlfiddle.com that cause the problem! When I just take a random sample from my data, I don't have any problem converting it at all! It means that are some cells that are causing the problem, but that I am not sure how to narrow down! – Mayou Oct 01 '13 at 14:13
I would upload the file that contains the original data, but I am not sure if there is an option in SO to do that – Mayou Oct 01 '13 at 14:22
Can you copy/paste like 2 rows of data or something from this: select top 2 * from table where isnumeric(col) = 0 – Derek Oct 01 '13 at 14:36
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/38434/discussion-between-derek-kromm-and-mariam) – Derek Oct 01 '13 at 14:37
@DerekKromm Why can't you just look at the affected row count? – Kermit Oct 01 '13 at 14:58
@DerekKromm Sorry I don't have access to chat in SO. I do have access to chat in other Stack Exchange sites though. – Mayou Oct 01 '13 at 14:59
@DerekKromm Here is the result of `select top 10 SY from tab_test where isnumeric(SY)=0`. I don't think this could help you much because for some reason, these values don't cause any problem in being casted as float. -0.005433356876198163 0.0016805958714047496 -0.03880289401817995 0.0760114379547578 0.08506549438423029 -0.07692463738810329 0.042255709927375304 -0.17179389787222205 0.0 -2.0955317775481834 – Mayou Oct 01 '13 at 15:07
There must be something weird going on in your environment...how does 0.0 evaluate to not numeric? What happens if you do `select top 10 SY, isnumeric(SY) from tab_test where isnumeric(SY)=0` ? – Derek Oct 01 '13 at 15:12
I started a chat room in Database Administrator if it's ok with you! – Mayou Oct 01 '13 at 15:15
When I execute your last query, I get those values that I pasted, as well as a column of zeros. – Mayou Oct 01 '13 at 15:16
Question: if there is a tab after each number, would that cause it to be non-numeric? – Mayou Oct 01 '13 at 15:21
This is getting ugly, but run select mycolumn from mytable where isnumeric(mycolumn) = 0. Paste the first couple of rows from that result set, let's see what you've got. – Andrew Oct 01 '13 at 15:29
If `ISNUMERIC(NULL) = 0`, you don't need to add `OR mycolumn IS NULL` (at least, that addition wouldn't change anything). However, I think you meant instead to add `AND mycolumn IS NOT NULL`, so that only non-NULL non-convertible values can be returned. – Andriy M Oct 01 '13 at 16:01

score 0 · Answer 2 · answered Oct 01 '13 at 14:49

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

UnhandledExcepSean · Answer 3 · 2013-10-01T15:17:49.290

0

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

edited Oct 01 '13 at 15:17

answered Oct 01 '13 at 14:57

UnhandledExcepSean

12,504
2
35
51

Can you add an explanation of what this does? – Kermit Oct 01 '13 at 14:59
So this one would only extract all the `non numeric` characters from each cells in the column.. – Mayou Oct 01 '13 at 15:03
Then what would I do with that? This query will extract characters like `-` or `E-`, that doesn't mean those numbers that contain these characters can't be converted to float.. – Mayou Oct 01 '13 at 15:09
You'll have to look at the data and see how it needs cleaned up. – UnhandledExcepSean Oct 01 '13 at 15:16

score 0 · Answer 4 · answered Oct 01 '13 at 15:31

0

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 '%.%.%'

answered Oct 01 '13 at 15:31

gvee

16,732
35
50

Detecting cells in column that cause error in SQL

4 Answers4

Linked