February 16, 2009 at 10:51 am
I have a table that stores address information. All of the columns are varchars. When I execute the following sql statement, I receive an error stating the it failed to convert varchar value '2908H' to data type int. If I comment out either side of the OR clause, it runs without any errors. I am guessing it has something to do with the order of which SQL Server is processing the query but thats only a guess. What could be the problem and how could I fix it.
SELECT CityNumber, COUNT(CityNumber) AS NumberOfParcels
FROM dbo.CountyRecords
WHERE
(
ISNUMERIC(HouseNumber) = 1
AND PreDirection = ''
AND StreetName = '175TH'
AND Suffix = 'ST'
AND CAST(LTRIM(RTRIM(HouseNumber)) as int) >= 9400
AND CAST(LTRIM(RTRIM(HouseNumber)) as int) <= 9499
AND PostDirection = ''
)
OR
(
ISNUMERIC(HouseNumber) = 1
AND PreDirection = ''
AND StreetName = 'CAMBRIDGE'
AND Suffix = 'PL'
AND CAST(LTRIM(RTRIM(HouseNumber)) AS INT) >= 17500
AND CAST(LTRIM(RTRIM(HouseNumber)) AS INT) <= 17600
AND PostDirection = ''
)
GROUP BY CityNumber
February 16, 2009 at 12:31 pm
[font="Verdana"]In another thread, Jeff Moden reminded me that numbers of the form '1d3' and '1e3' are legitimate.
A quick example:
select isnumeric('1d4'), isnumeric('1e4')
select cast('1d4' as int)
select cast('1e4' as int)
So I suspect you would be better off matching against a pattern of digits.
In terms of why it's failing with your example... I am a little confused. I thought SQL Server used lazy evaluation? You could use a CTE/derived table to pull out all of the fields that are numeric first, and then add your additional filtering criteria.
So you might end up with something like this:
with
NumericHouseNumbers as (
select HouseNumber,
PreDirection,
StreetName,
Suffix,
PostDirection,
CityNumber
from dbo.CountyRecords
where ltrim(rtrim(HouseNumber)) like '[0-9]' or
ltrim(rtrim(HouseNumber)) like '[0-9][0-9]' or
ltrim(rtrim(HouseNumber)) like '[0-9][0-9][0-9]' or
ltrim(rtrim(HouseNumber)) like '[0-9][0-9][0-9][0-9]' or
ltrim(rtrim(HouseNumber)) like '[0-9][0-9][0-9][0-9][0-9]' or
ltrim(rtrim(HouseNumber)) like '[0-9][0-9][0-9][0-9][0-9][0-9]'
)
select CityNumber,
count(1) as NumberOfParcels
from NumericHouseNumbers
where PreDirection = '' and
PostDirection = '' and (
StreetName = '175TH' and
Suffix = 'ST' and
cast(ltrim(rtrim(HouseNumber)) as int) between 9400 and 9499
or
StreetName = 'CAMBRIDGE' and
Suffix = 'PL' and
cast(ltrim(rtrim(HouseNumber)) as int) between 17500 and 17600
)
group by
CityNumber;
[/font]
February 16, 2009 at 12:44 pm
I changed the cast from int to float to allow for values such as '1e4' and changed it to use a CTE and I still manage to get the same conversion error as before. Weird!
;WITH Temp AS
(
SELECT *
FROM dbo.CountyRecords
WHERE ISNUMERIC(HouseNumber) = 1
)
SELECT CityNumber, COUNT(CityNumber) AS NumberOfParcels
FROM Temp
WHERE
(
PreDirection = ''
AND StreetName = '175TH'
AND Suffix = 'ST'
AND CAST(LTRIM(RTRIM(HouseNumber)) AS FLOAT) >= 9400
AND CAST(LTRIM(RTRIM(HouseNumber)) AS FLOAT) <= 9499
AND PostDirection = ''
)
OR
(
PreDirection = ''
AND StreetName = 'CAMBRIDGE'
AND Suffix = 'PL'
AND CAST(LTRIM(RTRIM(HouseNumber)) AS FLOAT) >= 17500
AND CAST(LTRIM(RTRIM(HouseNumber)) AS FLOAT) <= 17600
AND PostDirection = ''
)
GROUP BY CityNumber
February 16, 2009 at 12:49 pm
[font="Verdana"]I would have expected that to work! Is it still failing on the same number? ('2908H')[/font]
February 16, 2009 at 12:58 pm
Yes, the very same HouseNumber. The funny thing is is if I comment out the part that says "AND StreetName = '175TH'" it seems to work. Obviosuly not accurate because I need the street name in the query but it completes without error.
February 16, 2009 at 1:13 pm
[font="Verdana"]StreetName is a varchar, right? With values like 'CAMBRIDGE' I would hope so!
Can you run the query just filtering on StreetName? Maybe build it up in layered CTEs.
[/font]
February 16, 2009 at 1:15 pm
Right, it is a varchar. I don't understand how commenting that line out would cause it to execute successfully.
February 16, 2009 at 1:26 pm
[font="Verdana"]Me either. Here's hoping someone else on here has some ideas! Did you try creating multiple CTEs?[/font]
February 16, 2009 at 1:29 pm
No, I haven't tried using multiple CTE's yet. Might be worth looking into.
February 16, 2009 at 1:36 pm
a much longer thread on the ins and outs and liabilites of the IsNumeric function resulted in two enhanced user function s:IsReallyNumeric and IsReallyInteger. I saved them in my snippets.
try these and see if it improved your results:
[font="Courier New"]CREATE FUNCTION dbo.isReallyNumeric
(@num VARCHAR(64))
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))
DECLARE @pos TINYINT
SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))
RETURN CASE
WHEN PATINDEX('%[0-9.-]%', @num) = 0
AND @num NOT IN ('.', '-', '+', '')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
AND (((@pos = LEN(@num)+1) OR @pos = CHARINDEX('.', @num)) )
THEN 1
ELSE 0
END
END
GO
CREATE FUNCTION dbo.isReallyInteger
(@num VARCHAR(64))
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))
RETURN CASE
WHEN PATINDEX('%[0-9-]%', @num) = 0
AND CHARINDEX('-', @num) <= 1
AND @num NOT IN ('.', '-', '+', '')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
THEN 1
ELSE 0
END
END
GO[/font]
Lowell
February 16, 2009 at 1:46 pm
[font="Verdana"]Interesting. I thought the code looked a little wonky, so I did a quick test:
select dbo.isReallyInteger('1')
----
0
Last time I looked, 1 was an integer. 🙂
I suspect your PATINDEX needs a ^ at the beginning (after the square brackets, so it reads '%[^0-9-]%'). When I make that change, I get a better result.
[/font]
February 16, 2009 at 2:02 pm
damn you are right....
i copied this from the end of a long thread, figuring it was complete...i tried isReallyInteger('bob'), which returned 1 for true...I'm looking at it, but it's either what you found, or the return values got inverted.
I googled isReallyInteger, found other versions, but not the thread here on SSC.
Lowell
February 16, 2009 at 3:36 pm
Bruce W Cassidy (2/16/2009)
In another thread, Jeff [font="Arial Black"]Modan [/font]reminded me that numbers of the form '1d3' and '1e3' are legitimate.
Heh... and Bruce W [font="Arial Black"]Cassody [/font]used the information very well. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2009 at 3:41 pm
Jeff Moden (2/16/2009)
Bruce W Cassidy (2/16/2009)
In another thread, Jeff [font="Arial Black"]Modan [/font]reminded me that numbers of the form '1d3' and '1e3' are legitimate.Heh... and Bruce W [font="Arial Black"]Cassody [/font]used the information very well. 😛
[font="Verdana"]Ooops! Sorry Jeff. I really should proof-read more, huh?[/font]
February 16, 2009 at 3:46 pm
For more on the supposed problems with ISNUMERIC, please visit the following thread...
http://www.sqlservercentral.com/Forums/Topic243646-8-1.aspx#BM245050
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply