VARCHAR lat and long data fails when converting to NUMERIC, yet ISNUMERIC never returns 0

  • Hi All:

    This one has me stumped!

    I have lat and long data that comes in as VARCHAR. Some rows have NULL values. In the course of the testing I have converted all NULL values into '0'.

    #t looks like:

    CREATE TABLE #t (ID BIGINT NOT NULL, vppblat VARCHAR(15) NOT NULL, vppblong VARCHAR(15) NOT NULL)

    When I run

    select id

    ,lat

    --,convert(numeric(18,12),lat) as numlat,

    ,long

    --,convert(numeric(18,12),long) as numlong

    ,isnumeric(long)

    FROM #t

    where isnumeric(long)=0

    no rows are returned. In other words, all the [long] values can be cast as numeric.

    However, when I run:

    select id

    ,lat

    ,convert(numeric(18,12),lat) as numlat,

    ,long

    ,convert(numeric(18,12),long) as numlong

    ,isnumeric(long)

    FROM #t

    I get: "Error converting data type varchar to numeric."

    The values in the table for [long] are less than -165.00000000 and have up to but not more than 6 digits after the decimal, with up to 2 trailing zeros, for a total of 8 digits after the decimal point. Actually, to be precise, the values are MORE than (in other words -165... up to ~120...)

    Any thoughts would be appreciated. Thanks in advance.

  • Need a sample of the information in #t (aka, some row inserts), and your code doesn't match your table. But yeah, need to see what's in the table that's specifically failing. We need to be able to repeat your error locally.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Damnit, double post, sorry.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Hi

    ISNUMERIC may not be doing what you are expecting.

    Try the following

    SELECT lat

    FROM #t

    WHERE lat like '%[^0-9.-]%';

    SELECT long

    FROM #t

    WHERE long like '%[^0-9.-]%';

  • Further on mickyT's post, ISNUMERIC returns 1 for more than just numbers as the sample below shows

    😎

    USE tempdb;

    GO

    ;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

    ,NUMS(N) AS (SELECT TOP(255) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1, T T2, T T3, T T4)

    SELECT

    NM.N

    ,CHAR(NM.N) AS N_CHAR

    ,ISNUMERIC(CHAR(NM.N)) AS IS_NUM

    FROM NUMS NM

    WHERE ISNUMERIC(CHAR(NM.N)) = 1

    Results

    N N_CHAR IS_NUM

    -------------------- ------ -----------

    9 1

    10

    1

    11 1

    12 1

    13

    1

    36 $ 1

    43 + 1

    44 , 1

    45 - 1

    46 . 1

    48 0 1

    49 1 1

    50 2 1

    51 3 1

    52 4 1

    53 5 1

    54 6 1

    55 7 1

    56 8 1

    57 9 1

    92 \ 1

    128 € 1

    160   1

    162 ¢ 1

    163 £ 1

    164 ¤ 1

    165 ¥ 1

  • It's even "worse" than that, although it works exactly as advertised. Please see the following article.

    http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (8/12/2014)


    It's even "worse" than that, although it works exactly as advertised. Please see the following article.

    http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/

    I was just about to update the post, adding a link to this article:-D

    😎

  • Thanks guys

    That was the article I wanted to link to before I ran out of time 🙂

  • IsNumeric returning 1 means that the value can be converted to one or more of the 'numeric' data types (int, float, numeric, money). Which one is the question....

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sorry about the code not exactly matching (table creation vs insert statement)... I edited what I was working on to make it simpler and as much as I tried to proof it typos slipped through...

    Thanks for the link on ISNUMERIC. I recall reading that a while back but it had slipped my mind. When I tested just now using the various 'numeric' datatypes, MONEY and SMALLMONEY succeeded, while all the others failed. The latitude column is not a problem, it is the longitude column.

    Thanks for the responses. dl

  • Don't suppose you're on SQL 2012? The TRY_PARSE added there is sooooo handy for this kind of thing.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • This to me looks completely silly, but this is what I did to make it 'work'.

    SELECT f.vpid#

    ,f.numlat As Latitude

    ,CONVERT(NUMERIC(12,8),f.[IntPart]) +

    CONVERT(NUMERIC(12,8),f.[Mantissa]) AS Longitude

    FROM

    (

    select vpid#, vppblat

    , convert(numeric(12,8),vppblat) as numlat,

    ,CHARINDEX('.',vppblong) As pointloc

    ,CASE WHEN vppblong<>'0' THEN LEFT(vppblong,CHARINDEX('.',vppblong)-1) ELSE 0 END AS IntPart

    ,'-0.'+CASE WHEN vppblong<>'0' THEN SUBSTRING(vppblong,CHARINDEX('.',vppblong)+1,LEN(RTRIM(vppblong))-CHARINDEX('.',vppblong)) ELSE '0' END AS Mantissa

    FROM #t

    ) f

    I stripped out the portion to the left of the decimal (as a string), the portion to the right of the decimal (as a string) adding to it '-0.'

    Then I converted both those to NUMERIC(12,8) and added them.

    It serves the purpose, but there must be a better way.

  • DavidL (8/13/2014)


    This to me looks completely silly, but this is what I did to make it 'work'.

    SELECT f.vpid#

    ,f.numlat As Latitude

    ,CONVERT(NUMERIC(12,8),f.[IntPart]) +

    CONVERT(NUMERIC(12,8),f.[Mantissa]) AS Longitude

    FROM

    (

    select vpid#, vppblat

    , convert(numeric(12,8),vppblat) as numlat,

    ,CHARINDEX('.',vppblong) As pointloc

    ,CASE WHEN vppblong<>'0' THEN LEFT(vppblong,CHARINDEX('.',vppblong)-1) ELSE 0 END AS IntPart

    ,'-0.'+CASE WHEN vppblong<>'0' THEN SUBSTRING(vppblong,CHARINDEX('.',vppblong)+1,LEN(RTRIM(vppblong))-CHARINDEX('.',vppblong)) ELSE '0' END AS Mantissa

    FROM #t

    ) f

    I stripped out the portion to the left of the decimal (as a string), the portion to the right of the decimal (as a string) adding to it '-0.'

    Then I converted both those to NUMERIC(12,8) and added them.

    It serves the purpose, but there must be a better way.

    Did you try using the code mickyT posted above to find the rows where the vppblong value contains some character other than the digits 0-9, decimal point (.), and negative sign (-)? Once you find examples of the bad data, you can figure out how best to clean it up. There's almost surely a simpler way than the gyrations you're going through now.

    If mickyT's code doesn't return any rows, you probably have non-printing characters in your vppblong column values. Rooting those out can be a little more frustrating, but I can walk you through that if necessary.

    Jason Wolfkill

  • I didn't look for non-numeric characters as I have a degree of trust that there aren't any. That may be misplaced, though, so to be complete I will for completeness' sake run that query. I was in a momentum of whacking away at it and the 'solution' I posted returned as many rows as the original dataset, so that is implicit proof that there are no non-numeric characters. If you have the time to post something for non-printing characters, I would appreciate it. thx.

  • wolfkillj (8/14/2014)

    Did you try using the code mickyT posted above to find the rows where the vppblong value contains some character other than the digits 0-9, decimal point (.), and negative sign (-)? Once you find examples of the bad data, you can figure out how best to clean it up. There's almost surely a simpler way than the gyrations you're going through now.

    If mickyT's code doesn't return any rows, you probably have non-printing characters in your vppblong column values. Rooting those out can be a little more frustrating, but I can walk you through that if necessary.

    I think I see what was happening.

    mickyT's code indeed returns many many rows with apparently invalid characters:

    SELECT long

    FROM #t

    WHERE long like '%[^0-9.-]%';

    resulted in 434k of about 650k rows.

    Some examples are:

    - 97.76892600

    - 87.90573800

    - 88.14191900

    - 88.21531600

    The above all have a ' ' space. If I REPLACE() that, I think it will work fine. Thanks for all your help. dl

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply