Error in select staement Conversion failed when converting the varchar value 'T' to data type int

1

select distinct
SUBSTRING(CONVERT(VARCHAR(MAX), invno),1,1) as invoiceno from MyTable

output
A
6
I
7
T
0
1
9
B
S

i need to remove 6 and 9 entries and i used the below query it is giving me the error
(Conversion failed when converting the varchar value 'T' to data type int.)

select * from Mytable
where SUBSTRING(CONVERT(VARCHAR(MAX), invno),1,1) not in (4,5)

2

try with
not in ('4','5')

1 Like
3

Yes, and you can simplify the first expression as well:

where invno not like '[45]%' /this has a chance to perform better, if an index seek is available/
or
where left(invno, 1) not in ('4', '5')

1 Like
4

One of those two statements must be wrong! Did you mean

NOT IN ('6', '9') ?

Separate point:

... CONVERT(VARCHAR(MAX), ...

don't use VARCHAR(MAX) unless the data requires it. Much slower processing than a specific size (<= 8000 characters for VARCHAR)