Solved

Convert char field to int in the where clause

Posted on 2006-07-18
3
12,077 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 250 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

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Why do some people recommend buying business VoIP from an ISP? What are the benefits to my company? What are the costs?
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses

615 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