Re: parsing records returned

DA Morgan wrote:
> 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.

Yeah, except 350h will become 3500 - might wind up in a better part of town :-)

Doesn't work for me though:

  1 select
translate('350h','0ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~!@#$%^&*()-', '0')
  2* from dual
SQL> / TRA

---
350

(there needs to be a mapping for each character, like
('350h','0ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~!@#$%^&*()-','00000000000000000000000000000000000000000000000000000000000000000000')
)

jg
--
@home.com is bogus
Hot blooded, and checkin' the scene:
http://www.signonsandiego.com/news/state/20060908-1142-ca-schwarzenegger-tape.html