Convert char field to int in the where clause

On Sybase, 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,
    CONSTRAINT p_app_number
    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.  

Any ideas?

Who is Participating?
Jan FranekConnect With a Mentor Commented:
Hi, I think, you can work around this problem using case statement. Try this modification to your view:

create view app_number_numonly
select convert( int, case when patindex('%[!-/,:-~a-zA-Z]%', number_value) = 0 then number_value else '0' end ) as 'join_field',
an.application_short_name, an.descr, an.number_id, an.number_value
from app_number an
where patindex('%[!-/,:-~a-zA-Z]%', number_value) = 0

and similar modification (adding join_field) to second view. Then use new join_filed to join these two views.

I have tried this technique on sample tables with varchar fields filled with mix of numbers and text and it worked, so I hope it will work for you too.

Jan FranekCommented:
Of course, you don't need views and you can add modified convert to your original attempt without views.
nbtzlp3Author Commented:
Thanks for the help.  I really appreciate it....
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.