Solved

Convert char field to int in the where clause

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Debugging PowerBuilder Nested Report / Datawindow Parameters 4 834
Cannot view sybase SCC in browser using Linux 5 1,789
SyBase SQL Syntax 7 314
StorageCraft ShadowProtect Sybase VSS? 3 583
We need a new way to communicate time sensitive or critical info.   The best part of my role at xMatters is visiting our clients all over the world to learn about how they operate their businesses, share insights that xMatters has gleaned across…
Note: This is the third blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   We’ve been talki…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

896 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now