Re: parsing records returned

From: <fitzjarrell_at_cox.net>
Date: 8 Sep 2006 08:08:19 -0700
Message-ID: <1157728099.915894.11470@i42g2000cwa.googlegroups.com>

Comments embedded.
Adam Sandler wrote:
> Hello,
>
> I'm taking over maintenance for an application and the users have asked
> for some new capabilities to be added.
>

This sounds like trouble in the making.

> 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.

Why? This 'requirement' expects a certain address format of all numbers, and nothing but numbers. I doubt any address list has such a structure.

>
> 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.
>

For your numeric addresses. But I'll bet you received an error when a non-numeric character popped up ...

> 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.
>

So much for your theory and the customer's modification.

> 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?

If all you have is an errant 'h' in your address number you COULD use replace in conjunction with substr() and instr():

SQL> select *
2 from address_test;

ADDRESS

325 E fourth
325h E fourth

SQL> select replace(substr(address, 1, instr(address, ' ') - 1), 'h') 2 from address_test
3 /

REPLACE(SUBSTR(ADDRESS,1,INSTR(ADDRESS,'

325
325

SQL> A call to to_number() and mod() would complete the construct:

SQL> select replace(substr(address, 1, instr(address, ' ') - 1), 'h') 2 from address_test
3* where mod(to_number(replace(substr(address, 1, instr(address, ' ') - 1), 'h')), 2) = 0
SQL> / no rows selected

325
325

SQL>
> 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.
>

What, exactly, are 'code smells'???

> 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?
>

It's been provided to you, again presuming you have a limited number of characters which can be appended to your address number.

> Suggestions are greatly appreciated... Thanks!

Double-posting doesn't get you any faster response. So, stop doing it.

David Fitzjarrell Received on Fri Sep 08 2006 - 10:08:19 CDT