Casting to Integer issue

1

I have the following CompanyCase (which is a varchar(16)) top 10 values in CompanyCase column

CompanyCase

MH 09090
MH 09089
MH 09088
MH 09087
MH 09086
MH 09085
MH 09084
MH 09083
MH 09082
MH 09081

For calculation, I got rid of the characters

SELECT TOP (10) 
     Replace( [CompanyCase],'MH','') 
      
  FROM [mydb].[dbo].[Company]
  where CompanyCase LIKE 'MH %'
  ANd endDate IS NULL
  order by checkInDate desc, CompanyCase desc

Returns:

09090
09089
09088
09087
09086
09085
09084
09083
09082
09081

I want to convert these top 10 values to integer as my ultimate goal is to calculate the difference between top value 09090 and min value 09081. But whenever I try to do the
following, I'm getting an error as shown below:

SELECT TOP (10) 
     CAST(Replace( [CompanyCase],'MH','')as int) AS Result 
      
  FROM [mydb].[dbo].[Company]
  where CompanyCase LIKE 'MH %'
  ANd endDate IS NULL
  order by checkInDate desc, CompanyCase desc

Error below:

 Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ' 015000  B' to data type int.

So the value ' 015000 B' exists somewhere in the CompanyCase column but it's not there in the top 10 values as shown
above and I'm wondering why casting it to integer is complaining while running CAST(Replace( [CompanyCase],'MH','')as int) AS Result

2

hi

it does the replace on all the rows in the column before taking TOP 10

you can take TOP 10 first and then try cast

3

You can always use

WHERE ISNUMERIC(Replace( [CompanyCase],'MH',''))=1

to be sure. A sql query is split into many sections and has a certain order how the SQL engine is taking care of the instruction. The top 10 is later then the convert as the error-message is showing:

value ' 015000 B' can not be converted by the sql-enigine. If you use SQL regular you should be aware of the order:

Secret To Optimizing SQL Queries - Understand The SQL Execution Order - YouTube