Converting a field from int to varchar during query

I have a query where I am writing the following statement:
select * from .....
where table1.field1 = table2.field2
....
....
....
field2 is a varchar field and field1 is an int field. Field2 contains the
data mapping data for field1 and also other unrelated data as character
strings.
How do I run this query without getting the error - "Syntax error converting
the varchar value 'XXXX' to a column of data type int."
Thanks,
Jignesh.
On Wed, 2 Feb 2005 13:07:04 -0800, Jig Bhakta wrote:

>I have a query where I am writing the following statement:
>select * from .....
>where table1.field1 = table2.field2
>...
>...
>...
>field2 is a varchar field and field1 is an int field. Field2 contains the
>data mapping data for field1 and also other unrelated data as character
>strings.
>How do I run this query without getting the error - "Syntax error converting
>the varchar value 'XXXX' to a column of data type int."
Hi Jignesh,
Try:
select * from .....
where CAST(table1.field1 AS varchar(10)) = table2.field2
....
....
....
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Nope, still getting the error.
"Hugo Kornelis" wrote:

> On Wed, 2 Feb 2005 13:07:04 -0800, Jig Bhakta wrote:
>
> Hi Jignesh,
> Try:
> select * from .....
> where CAST(table1.field1 AS varchar(10)) = table2.field2
> ....
> ....
> ....
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 3 Feb 2005 13:01:08 -0800, Jig Bhakta wrote:

>Nope, still getting the error.
Hi Jignesh,
In that case, you better post the complete query. Best is to include
CREATE TABLE statements and INSERT statements with some sample data to
reproduce the error.
Here's a small script that shows that my change does work on my system, so
your error is either caused by something else in your query, by something
strange in your data or by a SQL Server bug. If you post a script that
will reproduce the error, we can find which of these three is the cause.
-- Start of repro script that show the error is corrected
create table table1 (field1 int not null)
create table table2 (field2 varchar(20) not null)
go
insert table1 (field1) select 1 union all select 2
insert table2 (field2) select '1' union all select 'XXXX'
go
print 'Original'
print ''
select * from table1, table2
where table1.field1 = table2.field2
print '--'
go
print ''
print 'Corrected'
print ''
select * from table1, table2
where CAST(table1.field1 AS varchar(10)) = table2.field2
print '--'
go
drop table table1
drop table table2
go
-- Output:
Original
field1 field2
-- --
1 1
Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'XXXX' to a column of data type
int.
Corrected
field1 field2
-- --
1 1
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Jig,
Try this:
select * from ...
where
case when table1.field1 not like '%[^0-9]%' then cast(table1.field1 as
int) end
= table2.field2
The reason for the error is that a varchar = integer comparison
causes the varchar to be converted to an integer, so XXXX is
getting converted to an integer for the comparison. The CASE
statement causes the query to compare the column field1 with
field2 only if field1 is all digits. Otherwise, it compares NULL, which
won't cause an error.
The best thing to do would be not to store numerical and character
data in the same column! If it makes sense to compare two columns,
but they are not the same type, it's usually a sign that the database design
can be improved.
Steve Kass
Drew University
Jig Bhakta wrote:

>I have a query where I am writing the following statement:
>select * from .....
>where table1.field1 = table2.field2
>...
>...
>...
>field2 is a varchar field and field1 is an int field. Field2 contains the
>data mapping data for field1 and also other unrelated data as character
>strings.
>How do I run this query without getting the error - "Syntax error converting
>the varchar value 'XXXX' to a column of data type int."
>Thanks,
>Jignesh.
>