Convert char field to int in the where clause
Posted on 2006-07-18
On Sybase 126.96.36.199, I have a table with the following structure:
CREATE TABLE dbo.app_number
application_short_name char(15) NOT NULL,
number_id char(5) NOT NULL,
number_value char(20) NOT NULL,
descr char(60) NULL,
PRIMARY KEY CLUSTERED (application_short_name,number_id,number_value)
I have another table:
CREATE TABLE dbo.src_ait
(APP_ID char(255) not null,
ThreeDotHierarchy char(255) null,
Table src_ait is a landing table for importing data from another source system.
I need to join the two tables on app_number.number_value and src_ait.APP_ID. Both of these columns are char fields and may contain numbers or text values. The numbers may be formatted with leading zeros or not. In the result set, I only want the rows that have a numeric value and '00001' would be equal to '1'. The only way that I can think of to do this, without using a stored procedure, is to convert the app_number.number_value and src_ait.APP_ID fields to int in the where clause, like so:
select an.application_short_name, sa.AppID
from src_ait sa, app_number an
where convert(int, an.number_value) = convert(int, sa.AppID) and
patindex('%[!-/,:-~]%', an.number_value) = 0
This give me a conversion error as follows:
Syntax error during explicit conversion of CHAR value 'xxx ' to INT field.
This leads me to believe that the convert is taking place prior to the filter from the patindex. Is there anyway to make this query work?
I have tried creating a view as follows:
create view app_number_numonly
select an.application_short_name, an.descr, an.number_id, an.number_value
from app_number an
where patindex('%[!-/,:-~]%', number_value) = 0
and using this view in the select statement instead of the app_number table, but I get the same error message. I have verified that the view DOES NOT return any values with anything but numbers.
I know how to make this work in a stored procedure, but I would like to have a SQL statement that would do it instead.