September 24, 2015 at 1:47 pm
I have the following that defines one column in a view:
IIf(IsNumeric(Contracts.PurchaseOrder)=1,CAST(CONVERT(varchar,Contracts.PurchaseOrder,1) AS float),0) AS PurchaseOrder
This is done so I can sort this PurchaseOrder field numerically which works fine until a PurchaseOrder is found that passes the IsNumeric test but is not numeric.
What happened, and it seems to have been a copy and paste from someone's memo of the PurchaseOrder number, but the ASC code of 160 was entered into the PurchaseOrder field in the database as the 11th character of a normally 10 character field. With the ASC code of 160 in the field the IsNumeric still considered the field to be numeric, but the CAST(CONVERT... produced an ODBC error within our Access database whenever this view was used.
OK, my question is: Any way to improve IsNumeric so we don't experience another crash like this?
Thanks!
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
September 24, 2015 at 1:54 pm
This article by Jeff Moden covers your issue very well.
http://qa.sqlservercentral.com/articles/ISNUMERIC()/71512/
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 24, 2015 at 2:01 pm
Vic Rauch-303403 (9/24/2015)
CONVERT(varchar,
I recommend that you don't rely on the default size for varchar, there is some strange behaviour which will catch you out from time to time.
I use (20) for numerics-to-string and (40) for GUID-to-string as I find that easy to remember, large enough for worse-case, albeit not the tightest possible size
September 24, 2015 at 2:04 pm
Kristen-173977 (9/24/2015)
Vic Rauch-303403 (9/24/2015)
CONVERT(varchar,I recommend that you don't rely on the default size for varchar, there is some strange behaviour which will catch you out from time to time.
I use (20) for numerics-to-string and (40) for GUID-to-string as I find that easy to remember, large enough for worse-case, albeit not the tightest possible size
I agree with you on not relying on the default size. But there's nothing strange as the behavior is clearly documented on BOL.
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.
September 25, 2015 at 12:29 am
Luis Cazares (9/24/2015)
But there's nothing strange as the behavior is clearly documented on BOL.
Indeed, I could have phrased it better but it requires that folk have read the DOCs and know the circumstances where they will get varchar(1) rather than varchar(30) and in the main I don't think that is the case where I see varchar used without a size.
September 25, 2015 at 7:27 am
And let's not overlook the fact that the OP is converting a PO Number to a float. A numeric would be a much better option than float. And if you are using float you should also specify the precision.
_______________________________________________________________
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 25, 2015 at 7:39 am
Sean Lange (9/25/2015)
And let's not overlook the fact that the OP is converting a PO Number to a float. A numeric would be a much better option than float. And if you are using float you should also specify the precision.
Bad Choice!
Bad Choice!
Bad Choice!
Bad Choice!
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 25, 2015 at 7:47 am
Alvin Ramard (9/25/2015)
Sean Lange (9/25/2015)
And let's not overlook the fact that the OP is converting a PO Number to a float. A numeric would be a much better option than float. And if you are using float you should also specify the precision.Bad Choice!
Bad Choice!
Bad Choice!
Bad Choice!
Are you saying that to the OP or me?
_______________________________________________________________
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 25, 2015 at 7:55 am
Starting with v2012, we have the TRY_PARSE() function. It works very similar to CAST(), except instead of 'Conversion failed...' error, it will simply return NULL.
https://msdn.microsoft.com/en-us/library/hh213126.aspx
print cast('123'+char(160) as int);
Msg 245, Level 16, State 1, Line 12
Conversion failed when converting the varchar value '123 ' to data type int.
print try_parse('123'+char(160) as int);
NULL
print try_parse('123' as int);
123
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 25, 2015 at 8:18 am
Sean Lange (9/25/2015)
Alvin Ramard (9/25/2015)
Sean Lange (9/25/2015)
And let's not overlook the fact that the OP is converting a PO Number to a float. A numeric would be a much better option than float. And if you are using float you should also specify the precision.Bad Choice!
Bad Choice!
Bad Choice!
Bad Choice!
Are you saying that to the OP or me?
Saying FLOAT is a bad choice.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 25, 2015 at 8:28 am
Alvin Ramard (9/25/2015)
Sean Lange (9/25/2015)
Alvin Ramard (9/25/2015)
Sean Lange (9/25/2015)
And let's not overlook the fact that the OP is converting a PO Number to a float. A numeric would be a much better option than float. And if you are using float you should also specify the precision.Bad Choice!
Bad Choice!
Bad Choice!
Bad Choice!
Are you saying that to the OP or me?
Saying FLOAT is a bad choice.
That's what I thought but then I wasn't sure if you were saying my comment was a bad choice. Stupid internet and not being able to hear inflection...bah!!! 😀
_______________________________________________________________
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 25, 2015 at 8:38 am
Sean Lange (9/25/2015)
Alvin Ramard (9/25/2015)
Sean Lange (9/25/2015)
Alvin Ramard (9/25/2015)
Sean Lange (9/25/2015)
And let's not overlook the fact that the OP is converting a PO Number to a float. A numeric would be a much better option than float. And if you are using float you should also specify the precision.Bad Choice!
Bad Choice!
Bad Choice!
Bad Choice!
Are you saying that to the OP or me?
Saying FLOAT is a bad choice.
That's what I thought but then I wasn't sure if you were saying my comment was a bad choice. Stupid internet and not being able to hear inflection...bah!!! 😀
🙂
😎
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 25, 2015 at 1:25 pm
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
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
September 25, 2015 at 1:50 pm
Did you look into using TRY_PARSE() ?
Rather than telling you True or False if value is "numeric", it will tell you if the value will cast as a specific data type.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 25, 2015 at 1:59 pm
Can you also look at the number of characters to filter out those that have extra characters?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply