Menu
I have a table:
Account_Code | Desc 503100 | account xxx 503103 | account xxx 503104 | account xxx 503102A | account xxx 503110B | account xxx Where Account_Code is a varchar.
When I create a query below:
Select cast(account_code as numeric(20,0)) as account_code, descr from account where isnumeric(account_code) = 1 It runs well by returning all record that have a valid numeric value in account_code column.
But when I try to add another select, nested to prior sql:
select account_code,descr from ( Select cast(account_code as numeric(20, 0)) as account_code,descr from account where isnumeric(account_code) = 1 ) a WHERE account_code between 503100 and 503105 the query will return an error
Error converting data type varchar to numeric.
What is happening there?
I have already converted to numeric if account_code valid, but it seems the query is still trying to process a non valid record.
I need to use BETWEEN clause in my query.
304
To convert a varchar type to a numeric type, change the target type as numeric or BIGNUMERIC as shown in the example below: SELECT CAST('344' AS NUMERIC) AS NUMERIC; SELECT CAST('344' AS BIGNUMERIC) AS big_numeric; The queries above should return the specified value converted to numeric and big numeric.
Converting from one data type to another is a common operation you'll perform when working in databases. And SQL provides a useful utility known as CAST to achieve this. We'll see how it works in this article.
Use the CAST() function to convert an integer to a DECIMAL data type. This function takes an expression or a column name as the argument, followed by the keyword AS and the new data type. In our example, we converted an integer (12) to a decimal value (12.00).
SQL Server 2012 and Later
Just use Try_Convert instead:
TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.
Read more about Try_Convert.
SQL Server 2008 and Earlier
The traditional way of handling this is by guarding every expression with a case statement so that no matter when it is evaluated, it will not create an error, even if it logically seems that the CASE statement should not be needed. Something like this:
SELECT Account_Code = Convert( bigint, -- only gives up to 18 digits, so use decimal(20, 0) if you must CASE WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL ELSE X.Account_Code END ), A.Descr FROM dbo.Account A WHERE Convert( bigint, CASE WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL ELSE X.Account_Code END ) BETWEEN 503100 AND 503205 However, I like using strategies such as this with SQL Server 2005 and up:
SELECT Account_Code = Convert(bigint, X.Account_Code), A.Descr FROM dbo.Account A OUTER APPLY ( SELECT A.Account_Code WHERE A.Account_Code NOT LIKE '%[^0-9]%' ) X WHERE Convert(bigint, X.Account_Code) BETWEEN 503100 AND 503205 What this does is strategically switch the Account_Code values to NULL inside of the X table when they are not numeric. I initially used CROSS APPLY but as Mikael Eriksson so aptly pointed out, this resulted in the same error because the query parser ran into the exact same problem of optimizing away my attempt to force the expression order (predicate pushdown defeated it). By switching to OUTER APPLY it changed the actual meaning of the operation so that X.Account_Code could contain NULL values within the outer query, thus requiring proper evaluation order.
You may be interested to read Erland Sommarskog's Microsoft Connect request about this evaluation order issue. He in fact calls it a bug.
There are additional issues here but I can't address them now.
P.S. I had a brainstorm today. An alternate to the "traditional way" that I suggested is a SELECT expression with an outer reference, which also works in SQL Server 2000. (I've noticed that since learning CROSS/OUTER APPLY I've improved my query capability with older SQL Server versions, too--as I am getting more versatile with the "outer reference" capabilities of SELECT, ON, and WHERE clauses!)
SELECT Account_Code = Convert( bigint, (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%') ), A.Descr FROM dbo.Account A WHERE Convert( bigint, (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%') ) BETWEEN 503100 AND 503205 It's a lot shorter than the CASE statement.
66
There's no guarantee that SQL Server won't attempt to perform the CONVERT to numeric(20,0) before it runs the filter in the WHERE clause.
And, even if it did, ISNUMERIC isn't adequate, since it recognises £ and 1d4 as being numeric, neither of which can be converted to numeric(20,0).(*)
Split it into two separate queries, the first of which filters the results and places them in a temp table or table variable, the second of which performs the conversion. (Subqueries and CTEs are inadequate to prevent the optimizer from attempting the conversion before the filter)
For your filter, probably use account_code not like '%[^0-9]%' instead of ISNUMERIC.
(*) ISNUMERIC answers the question that no-one (so far as I'm aware) has ever wanted to ask - "can this string be converted to any of the numeric datatypes - I don't care which?" - when obviously, what most people want to ask is "can this string be converted to x?" where x is a specific target datatype.
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
