Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert char field to int in the where clause

Posted on 2006-07-18
3
Medium Priority
?
12,097 Views
Last Modified: 2011-08-18
On Sybase 12.5.0.3, 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
as
select an.application_short_name, an.descr, an.number_id, an.number_value
from app_number an
where patindex('%[!-/,:-~]%', number_value) = 0
go

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?





0
Comment
Question by:nbtzlp3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 14

Accepted Solution

by:
Jan Franek earned 1000 total points
ID: 17133974
Hi, I think, you can work around this problem using case statement. Try this modification to your view:

create view app_number_numonly
as
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
0
 
LVL 14

Expert Comment

by:Jan Franek
ID: 17133984
Of course, you don't need views and you can add modified convert to your original attempt without views.
0
 

Author Comment

by:nbtzlp3
ID: 17134039
Thanks for the help.  I really appreciate it....
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Want to know how to use Exchange Server Eseutil command? Go through this article as it gives you the know-how.
What we learned in Webroot's webinar on multi-vector protection.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question