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

Tag the dbms you're using. (Some product specific constructions there...) — jarlh, Jul 06 '17 at 10:09
It is not obvious to me why you are getting this error. Can you create a demo for us at [Rextester](http://www.rextester.com) and paste the link here? — Tim Biegeleisen, Jul 06 '17 at 10:11
http://rextester.com/CSY70642 The only thing I can think is ID in MYDAT is not what you think.. As this looks like it works.. how is ID defined in the schema - type, length etc.. — JGFMK, Jul 06 '17 at 10:27
so substring distinct on each character of MYDAT.ID... see if you've got alpha in a place you expect number... (3rd, 4th 5th char) — JGFMK, Jul 06 '17 at 10:35
SELECT DISTINCT SUBSTRING(ID, 3,1) FROM MYDAT; SELECT DISTINCT SUBSTRING(ID, 4,1) FROM MYDAT; SELECT DISTINCT SUBSTRING(ID, 5,1) FROM MYDAT; — JGFMK, Jul 06 '17 at 10:39
It's may be because it's doing casts before selection... So if you select and then do the cast on the results.. — JGFMK, Jul 06 '17 at 10:41

score 1 · Accepted Answer · answered Jul 06 '17 at 10:54

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

answered Jul 06 '17 at 10:54

JGFMK

8,425
4
58
92

score 0 · Answer 2 · answered Jul 06 '17 at 10:17

Cannot clearly state the cause , may be an issue with data. You can try for some workaround to avoid casting,

 create table tempdb (tempid varchar(3))

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 = SUBSTRING(MYDAT.ID, 3, 3)
where 
SUBSTRING(MYDAT.ID, 3, 3) <> '' and
SUBSTRING(MYDAT.ID, 3, 3) <> '000'and
SUBSTRING(MYDAT.ID, 3, 3)  NOT LIKE '%[^0-9]%'

Gordon Linoff · Answer 3 · 2017-07-06T10:57:53.610

The problem is that the where clause is not necessarily executed before the on clause. SQL Server can rearrange the operations.

I would guess that you really want to compare to the first three characters of MYDAT.ID. That simplifies things a bit, because you can use LEFT() as in the code below. In fact, your where conditions don't look right, so I fixed them.

The best solution is try_convert():

select tempdb.tempid
from tempdb left join
     MYDAT
     on tempdb.tempid = try_convert(int, left(MYDAT.ID, 3) )
where MYDAT.ID <> '' and
      left(MYDAT.ID, 3) <> '000' and
      left(MYDAT.ID, 3) NOT LIKE '%[^0-9]%';

In pre-SQL Server 2012 versions, you can use a case instead:

select tempdb.tempid
from tempdb left join
     MYDAT
     on tempdb.tempid = (case when left(MYDAT.ID, 1, 3) not like '%[^0-9]%')
                              then convert(int, left(MYDAT.ID, 3)
                         end)
where MYDAT.ID <> '' and
      left(MYDAT.ID, 3) <> '000' and
      left(MYDAT.ID, 3) NOT LIKE '%[^0-9]%';

case does guarantee the order of evaluation.

SQL error while converting varchar to int by joining two tables

3 Answers3