Error converting to float ignoring WHERE filter

Question

I have a table with a varchar column, PaymentRef, containing a variety of text and numeric data. Furthermore, there's a column, CurrencyAmount of datatype float:

PaymentRef               CurrencyAmount
----------               --------------
EUR 100,00               100
EUR 50,00                50
USD 25,00                25.2
Auth#: 98103             NULL
Auth#: 98104             NULL
Transferred from 2356    NULL
Transferred to 1356      NULL

Now whenever a record contains a PaymentRef on the form "EUR ##,##", "USD ##,##", etc. I need to compare the numeric value after the currency code, to the value of the CurrencyAmount column.

The following query works just fine:

SELECT CAST(REPLACE(SUBSTRING(PaymentRef, 5, 100), ',', '.') AS FLOAT) AS PaymentRefNumeric,
    CurrencyAmount
FROM MyTable
WHERE LEFT(PaymentRef, 3) IN ('EUR', 'USD', 'SEK')

However, if I try to compare the casted value to the CurrencyAmount column, as in:

WITH CTE AS (
    SELECT CAST(REPLACE(SUBSTRING(PaymentRef, 5, 100), ',', '.') AS FLOAT) AS PaymentRefNumeric,
        CurrencyAmount
    FROM MyTable
    WHERE LEFT(PaymentRef, 3) IN ('EUR', 'USD', 'SEK')
)
SELECT * FROM CTE WHERE PaymentRefNumeric <> CurrencyAmount

I get the following error:

Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to float.

I know that I can easily put the result of the first query into a temporary table, and then perform my comparison, but I'm wondering what the cause of this error is. I believe it has something to do with the query optimizer attempting to CAST the PaymentRef values to FLOAT, before applying the first WHERE filter. Is there a way to ensure the filter is applied before the values are cast? (That's why I tried using CTE's, but even without CTE's I still get the same error).

Answer 1

here's a workaround as described in my comment

WITH CTE AS (
    SELECT case when LEFT(PaymentRef, 3) IN ('EUR', 'USD', 'SEK')
                then CAST(REPLACE(SUBSTRING(PaymentRef, 5, 100), ',', '.') AS FLOAT) 
                else null
                end AS PaymentRefNumeric,
        CurrencyAmount
    FROM #t WHERE LEFT(PaymentRef, 3) IN ('EUR', 'USD', 'SEK')
)
SELECT * FROM CTE WHERE PaymentRefNumeric <> CurrencyAmount