Re: Converting a field from int to varchar during query

From: Steve Kass (skass_at_drew.edu)
Date: 02/04/05 

Date: Fri, 04 Feb 2005 02:19:41 -0500

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.
>
>

Relevant Pages