August 12, 2014 at 5:30 pm
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.
August 12, 2014 at 5:46 pm
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.
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
August 12, 2014 at 5:47 pm
Damnit, double post, sorry.
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
August 12, 2014 at 7:23 pm
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.-]%';
August 12, 2014 at 10:41 pm
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
August 12, 2014 at 10:59 pm
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
Change is inevitable... Change for the better is not.
August 12, 2014 at 11:03 pm
Jeff Moden (8/12/2014)
It's even "worse" than that, although it works exactly as advertised. Please see the following article.
I was just about to update the post, adding a link to this article:-D
😎
August 13, 2014 at 12:44 am
Thanks guys
That was the article I wanted to link to before I ran out of time 🙂
August 13, 2014 at 2:16 am
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
August 13, 2014 at 9:27 am
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
August 13, 2014 at 9:45 am
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
August 13, 2014 at 12:44 pm
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.
August 14, 2014 at 11:23 am
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
August 14, 2014 at 11:30 am
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.
August 14, 2014 at 12:09 pm
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