Solved

Convert char field to int in the where clause

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Adults who share images on social media aren’t the only ones who need to worry about their privacy. Our culture’s tendency to share every move and celebration affects the privacy of our children, too.
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

679 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