IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New No, seems to be exactly what Oracle recommends for that task
As per this docco page: [link|http://download-uk.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/function.htm#79574|http://download-uk.o...unction.htm#79574] .

Why, what's (most) wrong with it -- the "neither pretty", or the "nor foolproof" bit?

As for the first, I'm sorry, but I suspect this is as pretty as it gets...

As for the second, how big a concern is this? Could it be ("suffuciently") fixed by expanding the 'From' string in your code (so as to include at least lowercase letters and quote characters, for instance)?

If not, and you want it "more foolproof", that could be done -- at the expense of becoming a LOT less "pretty", though... And possibly introducing some real performance concerns. So, before I present you my SUSTR()- and DECODE()-based solution, please tell us: How long is your "textfield"? How many gazillion records are there in your table? And what, if any, limits are there to your run-time-window and server resource usage?


   [link|mailto:MyUserId@MyISP.CountryCode|Christian R. Conrad]
(I live in Finland, and my e-mail in-box is at the Saunalahti company.)
Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link|http://z.iwethey.org/forums/render/content/show?contentid=71575|Ashton Brown]
New Wrong attitude
In this case, "more foolproof" should not be in quotes. Because the TRANSLATE solution is buggy. Depending on the rest of the application, one piece of unexpected input and you'll crash a process. Sucks when it is a batch process running in the middle of the night. (Been there, done that.)

Now I'll accept that Oracle hasn't built any function to make this (fairly common) task accomplishable in a simple and reliable way. However it isn't the programmer's fault for wanting the equivalent of s/^\\d//g. It exists in every other programming environment. It is faster to negate a small character class than it is to try to translate a large set of characters to nothing. It is more reliable.

It just doesn't happen to exist in Oracle. And Oracle doesn't care that their offered workaround is so suboptimal. (Probably because they know that nobody would ever make a purchase decision based on the presence of absence of this feature.)

Cheers,
Ben
To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act
- [link|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20|Nebraska Supreme Court]
New Trouble is, the process doesn't crash,
which seems to be more a feature of the ETL tool, than anything else. As soon as Oracle returns an 'Invalid Number' error, it just gives the indication that it has read in all the records it needs to, and continues with the rest of the program. Which is a bit scary.

The fun part has been altering a colleague's attitude from 'Your program is wrong!' to 'Oh, uh, you fixed my SQL? Umm, thanks...' :)
Two out of three people wonder where the other one is.
     Oracle: An inverse of the TRANSLATE function? - (Meerkat) - (10)
         No, seems to be exactly what Oracle recommends for that task - (CRConrad) - (2)
             Wrong attitude - (ben_tilly) - (1)
                 Trouble is, the process doesn't crash, - (Meerkat)
         OK, here's the "foolproof-but-fugly" solution: - (CRConrad) - (2)
             Believe me I came close to writing something similar - (Meerkat)
             *shudder* -NT - (folkert)
         What version of Oracle? - (broomberg) - (3)
             That's cool - (ben_tilly)
             Gah. - (admin)
             Well spotted! - (Meerkat)

G'day, mate, throw another Mozart on the harpsichord!
46 ms