Re: parsing records returned

Adam Sandler wrote:

> 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 think this is a really bad idea and should not be implemented. But given that doing so is neither illegal nor immoral here is one solution:

SELECT TRANSLATE(address_field,
'0ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~!@#$%^&*()-', '0') FROM table;

Extend the second parameter with every keystroke possible on the keyboard excluding integers. There will be nothing left except numbers.

-- 
Daniel A. Morgan
University of Washington
Puget Sound Oracle Users Group