Sql when isnumeric

SQL is a descriptive language. It does not specify the order of operations. The problem that you are well, were having is that the where does not do the filtering before the conversion takes place. Order of operations, though, is guaranteed for a case statement. So, the following will work:. The issue has nothing to do with the particular numeric format you are converting to or with the isnumeric function.

It is simply that the ordering of operations is not guaranteed. If you're on an earlier version then you can write a function that'll convert to a decimal or NULL if it won't convert. This uses the XML conversion functions that don't throw errors when the number won't fit ;. I would guess that you have value in the reserve column that passes the isnumeric test but will not cast to decimal. Just a heads up on isnumeric; if the string contains some numbers and an 'E' followed by some numbers, this is viewed as an exponent.

Example, select isnumeric 'E0' returns 1. I had this same problem and it turned out to be scientific notation such as '1. Try bypassing these and see if it works.

You'll have to write code to convert these to something usable or reformat your source file so it doesn't store any numbers using scientific notation. I am also facing this issue and I solved by below method. I am sharing this because it may helpful to some one.

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 10 years, 1 month ago. Active 3 years, 5 months ago. Sign in. United States English. Ask a question. Quick access. Search related threads. Remove From My Forums. Answered by:. Archived Forums. This is the place for advice and discussions 0 2. Sign in to vote. From what I have seen so far you cannot use as statement such as R Campbell.

Monday, March 27, PM. Tuesday, March 28, AM. Also the CAST function itself is quirky in that it cannot convert perfectly reasonable string-representations of integers into integers. This cuts out a lot of the apparent quirkiness.

Here is a small test-rig. You can only get part of the way with the LIKE wildcards, because you cannot specify quantifiers. So is IsNumeric an out and out rogue function?

Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed. Phil Factor real name withheld to protect the guilty , aka Database Mole, has 40 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early s, he has remained resolutely anonymous throughout his career. View all articles by Phil Factor.