SQL error while converting varchar to int by joining two tables

Question

I need to find missing numbers in my database. I am comparing two databases, tempdb created in query with numbers 1 - 999 and MYDAT.

MYDAT looks like:

+-------+
|  id   |
+-------+
| A-001 |
| A-002 |
| A-004 |
| A-... |
| A-952 |
| A-... |
+-------+

I am running this query:

declare @tempid int

set @tempid = 1

create table tempdb (tempid int)

while @tempid < 1000
begin
    insert into tempdb values(@tempid)
    set @tempid = @tempid + 1
end

select tempdb.tempid from tempdb
left join MYDAT on tempdb.tempid = CAST(SUBSTRING(MYDAT.ID, 3, 3) as INT)
where
MYDAT.ID IS NULL and
SUBSTRING(MYDAT.ID, 3, 3) <> '' and
SUBSTRING(MYDAT.ID, 3, 3) <> '000'and
SUBSTRING(MYDAT.ID, 3, 3)  NOT LIKE '%[^0-9]%'

drop table tempdb

Without droping temdb, select * from tempdb looks good and i am getting what i want.

The part with selecting and converting data from MYDAT works good and i am getting only integers

select CAST(SUBSTRING(MYDAT.ID, 3, 3) as INT) fom MYDAT
where 
SUBSTRING(MYDAT.ID, 3, 3) <> '' and
SUBSTRING(MYDAT.ID, 3, 3) <> '000'and
SUBSTRING(MYDAT.ID, 3, 3)  NOT LIKE '%[^0-9]%'

I am getting an error "converting varchar to int" but i have no idea why. When i change left join to right join, i get no errors.

I also checked both databases manually and there are no strings or characters, only integers.

I also tried the CONVERT() but with same result.

Any suggestions or ideas what is the problem?

Edit:

1 - I see one mistake as i tried it on rextester. I added MYDAT.ID IS NULL to the query so i get correct results.

2 - Examples I need this: http://rextester.com/KFG73206

But CAST or CONVERT just does not seems to work http://rextester.com/WJIAH52304

Answer 1

You did say 'missing numbers' so things in tempdb that aren't in MYDAT are what you're after? If so see: http://rextester.com/HCB88714