August 7, 2009 at 10:17 am
ronmoses (8/7/2009)
Dang it, I knew that was the answer and didn't pick it. That seemed like the obvious "otherwise, why would I ask the question?" answer. But no, I had to go and think about it. Stupid me. 🙂
Ditto :-). That said, I would love to hear from the people who thought the answer for a CONVERT(int, {anything}) would be either 0.0 or 0,.0!
August 7, 2009 at 11:47 am
I'd like to seee the explanation expanded to explain that select convert(money,'0,.0') would work, and that is why isNumeric is returning true (i.e. that isnumeric is ignoring the comma because that's what convert does for money).
Try:
select convert(money,'0,,,0,.0,,,0')
August 8, 2009 at 9:12 am
I encountered this issue a couple of month ago and i solved it with the following udf from ASPFAQ.com
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) 0
AND @num NOT LIKE '%-%'
THEN
1
ELSE
0
END
END
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
August 10, 2009 at 12:59 am
kevin.l.williams (8/7/2009)
isnumeric is just evaluating valid characters.This will produce 1 also
select isnumeric('-$,')
Hi Kevin,
I think that is not true.
Try select isnumeric('$$') for example.
It will evaluate to false, although this character ($) was no issue in your previous example.
The reason why your example is returning true, is probably the fact that it can be cast to (small)money.
Try finding an example that is not convertible to a numeric datatype but that returns true in this function.
Best Regards,
Chris Büttner
August 10, 2009 at 5:24 am
Try finding an example that is not convertible to a numeric datatype but that returns true in this function.
That's impossible 😛 as per the definition of the function, ISNUMERIC only returns true if the input is convertible to one of the numeric data types.
August 10, 2009 at 10:18 am
Christian Buettner (8/10/2009)
Hi Kevin,I think that is not true.
Try
select isnumeric('$$')for example.It will evaluate to false, although this character ($) was no issue in your previous example.
The reason why your example is returning true, is probably the fact that it can be cast to (small)money.
My mistake. Thanks for pointing this out. I always thought this function just didn't work properly. Now I see I just didn't understand it's purpose. Any idea why Microsoft didn't implement ISMONEY() ISINT() or ISFLOAT()? ISNUMERIC() seems almost useless for testing before an insert.
August 11, 2009 at 12:09 am
That is actually a good question and unfortunately I have no idea 🙁
Best Regards,
Chris Büttner
August 11, 2009 at 1:13 am
ISNUMERIC() checks whether the input is valid numeric data, that also includes money data type, thats why it used to return 1 for isnumeric('0,.0') cause its a valid money type. On the other hand integer data type is limited to numbers only...
August 12, 2009 at 12:09 pm
"Conversion failed when converting the varchar value '0,.0' to data type int."
No SQL product that I could find returned "error converting to a numeric" when trying to convert a varchar value to Int.
Why make the correct answer wrong?
Are you practicing up to write certification exam questions where the least wrong answer is the right one?
November 21, 2009 at 2:14 am
Yeap, I got it!
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply