I have some rows I'm trying to cast to integer to get the last number in sequence.

This is my original query.

```
SELECT
  MAX(CAST(REPLACE(ItemName, 'CA', '') AS INT)) + 1
FROM InventoryItem ii
JOIN InventoryItemDepartment iid
  ON ii.ItemCode = iid.ItemCode
WHERE iid.DepartmentCode = 'Filters'
AND ItemName LIKE 'CA4%____'
AND CAST(REPLACE(ItemName, 'CA', '') AS INT) < 41000

```

However I get an error message:
 `Error (1,1): Conversion failed when converting the nvarchar value '41020-S' to data type int.`

Obviously, I'm aware what this message means. However I'm confused why its throwing the error, as the `WHERE` clauses I've specified are for the sole purpose of excluding records which might fail the cast.

If I modify the query to just select the original values, without any replacing or casting...

```
SELECT
  ItemName
FROM InventoryItem ii
JOIN InventoryItemDepartment iid
  ON ii.ItemCode = iid.ItemCode
WHERE iid.DepartmentCode = 'Filters'
AND ItemName LIKE 'CA4%____'

```

This returns some data like so:

```
CA40000
CA40001
CA40002
CA40003
CA40004
CA40005
.... etc

```

The troublesome value '41020-S' (Originally 'CA41020-S' after the Replace) isn't included in the second debug result set, as I'd expected.

Can anyone help me explain this weird behaviour, and how I might possibly overcome it?