parsing records returned

Hello,

I'm taking over maintenance for an application and the users have asked for some new capabilities to be added.

In the database behind this app, there's an address table. The user's client app can already let them search for rows by address number. Now they want to be able to search by even address only or odd address only.

So I did some remainder division (MOD) by 2 and where the result was 0 an even address was found and where the result is 1 an odd result was found.

But here's where things get fouled up... there's a record where the address has not only numbers but a letter as well... there's 1 row where the address is 325; there's 1 row where the address is 325h. The 'h' is there to represent 'half'... as in 325 1/2... or a unit which resides directly above another unit.

I'm a little stuck on how to proceed. I'm not sure on how to account for a potential mix of letters and characters? I thought about returning all the address number records into a collection in the client app, iterating through the collection -- parsing the string a character at a time and looking for only integers... unfortunately, this solution is rife with code smells and performance takes a hit because after iterating through the collection, I have to hit the database for a second time with each address number, which is really only an integer and no characters, as a where clause in the SQL statement.

Is there any other way I could do this? Is there a SQL way in Oracle to make sure only I do the MOD division on something which is all integers in the set of ASCII codes?

Suggestions are greatly appreciated... Thanks! Received on Fri Sep 08 2006 - 09:28:50 CDT