Re: parsing records returned

Adam Sandler wrote:
> 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!

Write a db function that converts any input sequence (i.e. digits only and mixed) to a number. Or even better, create a db function that converts any input to 0 and 1.

Then create a function based index on this. I'd read in Oracle docs which type of index is most appropriate for this case (maybe bitmap).

HTH         robert Received on Fri Sep 08 2006 - 15:19:08 CDT