September 6, 2018 at 11:31 pm
Comments posted to this topic are about the item ISNUMERIC function
September 7, 2018 at 12:19 am
Nice, simple question to end the week on, thanks Kiran
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
September 7, 2018 at 1:10 am
I got caught out. No one I know uses IsNumeric() to check if a string is numeric because there are many cases where it says true where if you attempt to store the value in an actual numeric type it fails.
September 7, 2018 at 2:28 am
peter.row - Friday, September 7, 2018 1:10 AMI got caught out. No one I know uses IsNumeric() to check if a string is numeric because there are many cases where it says true where if you attempt to store the value in an actual numeric type it fails.
which is where TRY_CONVERT comes in very handy
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
September 7, 2018 at 4:13 am
Interestingly enough, the referenced article doesn't really say anything about spaces. I'd even go so far as to say the article is just about as useless as the function itself...
September 7, 2018 at 8:13 am
Rune Bivrin - Friday, September 7, 2018 4:13 AMInterestingly enough, the referenced article doesn't really say anything about spaces. I'd even go so far as to say the article is just about as useless as the function itself...
Indeed!!! Here is an excellent spackle article on this very function. http://www.sqlservercentral.com/articles/71512/
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 7, 2018 at 8:17 am
Stewart "Arturius" Campbell - Friday, September 7, 2018 2:28 AMpeter.row - Friday, September 7, 2018 1:10 AMI got caught out. No one I know uses IsNumeric() to check if a string is numeric because there are many cases where it says true where if you attempt to store the value in an actual numeric type it fails.which is where TRY_CONVERT comes in very handy
Not very handy when you're trying to do a set based operation on a table of strings that should be numbers though.
September 7, 2018 at 8:21 am
peter.row - Friday, September 7, 2018 8:17 AMStewart "Arturius" Campbell - Friday, September 7, 2018 2:28 AMpeter.row - Friday, September 7, 2018 1:10 AMI got caught out. No one I know uses IsNumeric() to check if a string is numeric because there are many cases where it says true where if you attempt to store the value in an actual numeric type it fails.which is where TRY_CONVERT comes in very handy
Not very handy when you're trying to do a set based operation on a table of strings that should be numbers though.
Why? It can be used in a set based approach. But really the problem here is the table of strings that should be numbers!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 7, 2018 at 8:29 am
Sean Lange - Friday, September 7, 2018 8:21 AMpeter.row - Friday, September 7, 2018 8:17 AMStewart "Arturius" Campbell - Friday, September 7, 2018 2:28 AMpeter.row - Friday, September 7, 2018 1:10 AMI got caught out. No one I know uses IsNumeric() to check if a string is numeric because there are many cases where it says true where if you attempt to store the value in an actual numeric type it fails.which is where TRY_CONVERT comes in very handy
Not very handy when you're trying to do a set based operation on a table of strings that should be numbers though.
Why? It can be used in a set based approach. But really the problem here is the table of strings that should be numbers!!!
Think importing of data into holding table, which you then validate reject data that is bad and transfer to final tables that is good. This is not a 1 off and is something potentially kicked off by user or a web service etc...
Hmmm, probably didn't think of this because we still have to support SQL 2008 R2 where TRY_CONVERT() doesn't exist.
September 7, 2018 at 8:43 am
peter.row - Friday, September 7, 2018 8:29 AMSean Lange - Friday, September 7, 2018 8:21 AMpeter.row - Friday, September 7, 2018 8:17 AMStewart "Arturius" Campbell - Friday, September 7, 2018 2:28 AMpeter.row - Friday, September 7, 2018 1:10 AMI got caught out. No one I know uses IsNumeric() to check if a string is numeric because there are many cases where it says true where if you attempt to store the value in an actual numeric type it fails.which is where TRY_CONVERT comes in very handy
Not very handy when you're trying to do a set based operation on a table of strings that should be numbers though.
Why? It can be used in a set based approach. But really the problem here is the table of strings that should be numbers!!!
Think importing of data into holding table, which you then validate reject data that is bad and transfer to final tables that is good. This is not a 1 off and is something potentially kicked off by user or a web service etc...
Hmmm, probably didn't think of this because we still have to support SQL 2008 R2 where TRY_CONVERT() doesn't exist.
But why would that prevent using TRY_CONVERT? It returns NULL when it can't be converted. Seems like the perfect time to use it to me. You insert only those rows where TRY_CONVERT is not null, every row that is NULL failed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 7, 2018 at 8:50 am
Sean Lange - Friday, September 7, 2018 8:43 AMpeter.row - Friday, September 7, 2018 8:29 AMSean Lange - Friday, September 7, 2018 8:21 AMpeter.row - Friday, September 7, 2018 8:17 AMStewart "Arturius" Campbell - Friday, September 7, 2018 2:28 AMpeter.row - Friday, September 7, 2018 1:10 AMI got caught out. No one I know uses IsNumeric() to check if a string is numeric because there are many cases where it says true where if you attempt to store the value in an actual numeric type it fails.which is where TRY_CONVERT comes in very handy
Not very handy when you're trying to do a set based operation on a table of strings that should be numbers though.
Why? It can be used in a set based approach. But really the problem here is the table of strings that should be numbers!!!
Think importing of data into holding table, which you then validate reject data that is bad and transfer to final tables that is good. This is not a 1 off and is something potentially kicked off by user or a web service etc...
Hmmm, probably didn't think of this because we still have to support SQL 2008 R2 where TRY_CONVERT() doesn't exist.But why would that prevent using TRY_CONVERT? It returns NULL when it can't be converted. Seems like the perfect time to use it to me. You insert only those rows where TRY_CONVERT is not null, every row that is NULL failed.
See the bit where I said we still have to support SQL 2008R2 - TRY_CONVERT() does not exist in SQL 2008R2 and we haven't got the time and resource to support multiple versions of what should be the same thing.
Hopefully we will drop support for 2008 R2 soon and hence new TSQL syntax will be available for us to improve things.
September 11, 2018 at 6:53 am
nice and easy question, cheers
---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
September 11, 2018 at 7:53 am
peter.row - Friday, September 7, 2018 8:50 AMSean Lange - Friday, September 7, 2018 8:43 AMpeter.row - Friday, September 7, 2018 8:29 AMSean Lange - Friday, September 7, 2018 8:21 AMpeter.row - Friday, September 7, 2018 8:17 AMStewart "Arturius" Campbell - Friday, September 7, 2018 2:28 AMpeter.row - Friday, September 7, 2018 1:10 AMI got caught out. No one I know uses IsNumeric() to check if a string is numeric because there are many cases where it says true where if you attempt to store the value in an actual numeric type it fails.which is where TRY_CONVERT comes in very handy
Not very handy when you're trying to do a set based operation on a table of strings that should be numbers though.
Why? It can be used in a set based approach. But really the problem here is the table of strings that should be numbers!!!
Think importing of data into holding table, which you then validate reject data that is bad and transfer to final tables that is good. This is not a 1 off and is something potentially kicked off by user or a web service etc...
Hmmm, probably didn't think of this because we still have to support SQL 2008 R2 where TRY_CONVERT() doesn't exist.But why would that prevent using TRY_CONVERT? It returns NULL when it can't be converted. Seems like the perfect time to use it to me. You insert only those rows where TRY_CONVERT is not null, every row that is NULL failed.
See the bit where I said we still have to support SQL 2008R2 - TRY_CONVERT() does not exist in SQL 2008R2 and we haven't got the time and resource to support multiple versions of what should be the same thing.
Hopefully we will drop support for 2008 R2 soon and hence new TSQL syntax will be available for us to improve things.
I agree with Sean that TRY_CONVERT would be fine for what you're doing (except that it's not available in 2008.) ISNUMERIC can be fine if you understand what it does as well as the data you're querying but I never use it. For what you're describing - it sounds like you can also eadily get the job done using PATINDEX or LIKE.
-- Itzik Ben-Gan 2001
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply