September 25, 2015 at 2:08 pm
Vic Rauch-303403 (9/25/2015)
Thank you for all the comments. My question was how to make IsNumeric tell me the truth about an alpha field. UDP Broadcaster did post a link to a great discussion of IsNumeric with a way to find if a string really is numeric. I will be including that where the IsNumeric currently is used.But all all the rest of the comments: Thank you for them too. I am a true rookie with SQL Server, so far self taught (I'm thinking that would show!), and always looking for things to stay away from, and better ways to do what I already "know."
Again, thank you all for your input!
Vic
But the point is that IsNumeric IS telling you the truth. You are looking for all numbers, not what IsNumeric actually does. The article referenced previously is exactly what you are looking for.
To repeat what is in Books on Line, and referenced in Jeff Moden;s article, IsNumeric does not look for numbers, it returns 1 if a value can be successfully be CONVERTED or CAST to a numeric data type.
That means ANY numeric type.
Run the code below. The converts and casts to integers will be successful, the converts and casts to numerics and floats will fail.
But IsNumeric returns success. It's telling you that "+" can successfully be converted to a numeric type.
SELECT CONVERT(int, '+')
SELECT CONVERT(float, '+')
SELECT CONVERT(numeric(12,2), '+')
SELECT CAST ('+' as int)
SELECT CAST ('+' as float)
SELECT CAST('+' as numeric(12,2))
SELECT IsNumeric('+')
Try_parse() and IsAllDigits will work. Read the article.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 26, 2015 at 1:45 am
Michael L John (9/25/2015)
IsNumeric does not look for numbers, it returns 1 if a value can be successfully be CONVERTED or CAST to a numeric data type.
Not sure about "successfully"?? as these return true for IsNumeric() but fail the CONVERT() for me, here, with my Locale settings
PRINT 'IsNumeric(''$1'')'
GO
SELECT IsNumeric('$1')
GO
PRINT 'CONVERT(numeric(12,2), ''$1'')'
GO
SELECT CONVERT(numeric(12,2), '$1')
GO
PRINT 'TRY_PARSE(''$1'' AS numeric(12,2))'
GO
SELECT TRY_PARSE('$1' AS numeric(12,2))
GO
PRINT 'IsNumeric(''$1'')'
GO
SELECT IsNumeric('$1')
GO
PRINT 'CONVERT(numeric(12,2), '',1'')'
GO
SELECT CONVERT(numeric(12,2), ',1')
GO
PRINT 'TRY_PARSE('',1'' AS numeric(12,2))'
GO
SELECT TRY_PARSE(',1' AS numeric(12,2))
GO
September 26, 2015 at 7:23 am
If the point of the OP was to test ISNUMERIC, then ample information and Jeff's article has been provided and I won't offer anything different. The function does what it does.
In the case of conversion, I'd recommend following Kristen's advice and never rely on the default length. The only thing you save is a couple of keystrokes, but that time will be made up for the first time you gets busted by it and have to debug the problem. It's just a safer approach.
If the real goal is to format a purchase order number, I think Alvin is exactly right. Even when they contain numeric data, most systems I've seen store them as strings. Some systems even append a single letter on the end to indicate revisions, which I personally believe is flawed. Yes, that limits them to 26 revisions and the revision should really be a separate column, but I digress. Unless you need to do math with the purchase order number, I would treat them as strings.
September 26, 2015 at 10:44 am
Kristen-173977 (9/26/2015)
Michael L John (9/25/2015)
IsNumeric does not look for numbers, it returns 1 if a value can be successfully be CONVERTED or CAST to a numeric data type.Not sure about "successfully"?? as these return true for IsNumeric() but fail the CONVERT() for me, here, with my Locale settings
PRINT 'IsNumeric(''$1'')'
GO
SELECT IsNumeric('$1')
GO
PRINT 'CONVERT(numeric(12,2), ''$1'')'
GO
SELECT CONVERT(numeric(12,2), '$1')
GO
PRINT 'TRY_PARSE(''$1'' AS numeric(12,2))'
GO
SELECT TRY_PARSE('$1' AS numeric(12,2))
GO
PRINT 'IsNumeric(''$1'')'
GO
SELECT IsNumeric('$1')
GO
PRINT 'CONVERT(numeric(12,2), '',1'')'
GO
SELECT CONVERT(numeric(12,2), ',1')
GO
PRINT 'TRY_PARSE('',1'' AS numeric(12,2))'
GO
SELECT TRY_PARSE(',1' AS numeric(12,2))
GO
It will return successful if it can be converted to ANY numeric type. Try an int, numeric(12, 3(or4)) or float. One of them will work!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 27, 2015 at 3:33 am
Michael L John (9/26/2015)
It will return successful if it can be converted to ANY numeric type. Try an int, numeric(12, 3(or4)) or float. One of them will work!
Thanks, that makes sense and would explain its apparently erratic behaviour.
Sadly I haven't been able to find any datatype that these two can be converted to - but I have only tried a handful of scale/precision for Numeric ... when I have time I'll try every possible variation 🙂
September 28, 2015 at 2:10 am
Kristen-173977 (9/27/2015)
Sadly I haven't been able to find any datatype that these two can be converted to - but I have only tried a handful of scale/precision for Numeric ... when I have time I'll try every possible variation 🙂
Try MONEY. Both the values you specify convert correctly to this on my test system, and it's just as much a numeric data type as INT or FLOAT are.
September 28, 2015 at 2:16 am
Typical! The one datatype I didn't think to try, thanks for that.
Makes much more sense now that I have learnt that ISNUMERIC returns true if value can be converted to "at least one" datatype.
But it must have suckered many a newbie into a false sense of security over the years ...
I suppose that will continue? Not sure Newbies are going to easily stumble over TRY_PARSE in favour of ISNUMERIC. BoL doesn't even mention TRY_PARSE on the ISNUMERIC page ...
September 28, 2015 at 6:59 am
Ideally you will never have invalid column values inserted in the first place, because once the bad data is in the table, then all the downstream applications have to tiptoe over it.
For example, if you have a VarChar column that should only contain values that are covertable to a specific data type (ie: Int or Date), then consider placing a check constraint on that column.
create table PurchaseOrders
(
PurchaseOrderNo varchar(20) not null
constraint cc_PurchaseOrderNo
check (try_parse(PurchaseOrderNo as int) is not null),
PurchaseDate varchar(20) not null
constraint cc_PurchaseDate
check (try_parse(PurchaseDate as date) is not null)
);
insert into PurchaseOrders ( PurchaseOrderNo, PurchaseDate )
values ('123'+char(160), getdate() );
Msg 547, Level 16, State 0, Line 19
The INSERT statement conflicted with the CHECK constraint "cc_PurchaseOrderNo".
insert into PurchaseOrders ( PurchaseOrderNo, PurchaseDate )
values ('123', '2015/02/30');
Msg 547, Level 16, State 0, Line 22
The INSERT statement conflicted with the CHECK constraint "cc_PurchaseDate".
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 28, 2015 at 7:05 am
Eric M Russell (9/28/2015)
For example, if you have a VarChar column that should only contain values that are covertable to a specific data type (ie: Int or Date), then consider placing a check constraint on that column.
Wouldn't it make more sense to just make the columns INT or DATETIME in the first place? That way you can guarantee you won't get the wrong data type in there!
September 28, 2015 at 7:57 am
paul.knibbs (9/28/2015)
Eric M Russell (9/28/2015)
For example, if you have a VarChar column that should only contain values that are covertable to a specific data type (ie: Int or Date), then consider placing a check constraint on that column.
Wouldn't it make more sense to just make the columns INT or DATETIME in the first place? That way you can guarantee you won't get the wrong data type in there!
Sure, if that would be a solution.
In this case, the data contains various combinations of data types. X10, 234, MJ5676 and so forth. This are pretty typically for purchase order data supplied by a customer.
The OP want's to be able to sort this data.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 28, 2015 at 11:36 am
paul.knibbs (9/28/2015)
Eric M Russell (9/28/2015)
For example, if you have a VarChar column that should only contain values that are covertable to a specific data type (ie: Int or Date), then consider placing a check constraint on that column.
Wouldn't it make more sense to just make the columns INT or DATETIME in the first place? That way you can guarantee you won't get the wrong data type in there!
Yes, that would make the most sense. Unfortunately most databases are modeled after the real world... meaning they are arbitrary, inconsistent, and inefficient.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 28, 2015 at 11:49 am
If this is all about how to sort alpha-numeric columns of varying lengths in a specific way, then I sometimes will order by an expression that does something like padding leading or trailing zeros or spaces.
declare @t as table(PurchaseOrderNo varchar(20) not null);
insert into @t ( PurchaseOrderNo )
values ('X10'),('234'),('MJ5676');
select * from @t
order by right( replicate('0',20) + PurchaseOrderNo, 20);
234
X10
MJ5676
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 30, 2015 at 10:35 am
In addition to Try_Parse, there is a new function Try_Convert, which does what IsNumeric should always have done.
set <output_value> = Try_Convert(<type>, <source_value>);
Try_Convert returns the source_value converted to the desired type, or NULL if the value cannot be converted.
So, where IsNumeric returns true if the source value could be converted to any possible numeric type, Try_Convert will return NULL if the value cannot be converted to the specified type.
declare @i int;
set @i = Try_Convert(int, '123'); -- @i becomes 123
set @i = Try_Convert(int, '+'); -- @i becomes NULL, while IsNumeric says 1
September 30, 2015 at 10:39 am
Kim Crosser (9/30/2015)
In addition to Try_Parse, there is a new function Try_Convert, which does what IsNumeric should always have done.set <output_value> = Try_Convert(<type>, <source_value>);
Try_Convert returns the source_value converted to the desired type, or NULL if the value cannot be converted.
So, where IsNumeric returns true if the source value could be converted to any possible numeric type, Try_Convert will return NULL if the value cannot be converted to the specified type.
declare @i int;
set @i = Try_Convert(int, '123'); -- @i becomes 123
set @i = Try_Convert(int, '+'); -- @i becomes NULL, while IsNumeric says 1
When I use Try_Convert(int, '+') it gives me 0 instead of NULL.
September 30, 2015 at 10:44 am
Luis Cazares (9/30/2015)
Kim Crosser (9/30/2015)
In addition to Try_Parse, there is a new function Try_Convert, which does what IsNumeric should always have done.set <output_value> = Try_Convert(<type>, <source_value>);
Try_Convert returns the source_value converted to the desired type, or NULL if the value cannot be converted.
So, where IsNumeric returns true if the source value could be converted to any possible numeric type, Try_Convert will return NULL if the value cannot be converted to the specified type.
declare @i int;
set @i = Try_Convert(int, '123'); -- @i becomes 123
set @i = Try_Convert(int, '+'); -- @i becomes NULL, while IsNumeric says 1
When I use Try_Convert(int, '+') it gives me 0 instead of NULL.
I also get 0 as the answer.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply