(Sorry for the delayed response, I had a doctor's appointment [following my previous post] from which I did not return until just a few minutes ago.)
Point well taken,
Karl. But with an ever-so-slight adjustment to the code (to generalise the solution for any number of digits or characters), and a slight disciplining of the specification of the function's incoming parameter values, one can deal with the issue you mention.
The disciplining of the parameter values is that once
WebERM decides upon the parameter value, he sticks with that value.
To illustrate, I've modified the DEPTNO of two rows to reflect both single-digit and greater-than-two-digit searches. I have also chosen (for ease of reading the code) "/" to be the delimeter of choice (but the delimiter can become any characters, so long as delimiter in
the function definition is the delimiter in
the function call).:
Code:
select ename,deptno from emp order by 2,1;
ENAME DEPTNO
-------------------- ----------
LaDoris Ngao 5
Mark Quick-To-See 10
Colin Magee 31
Midori Nagayama 31
Henry Giljum 32
Yasmin Sedeghi 33
Mai Nguyen 34
Radha Patel 34
Andre Dumas 35
Elena Maduro 41
George Smith 41
Molly Urguhart 41
Akira Nozaki 42
Roberta Menchu 42
Vikram Patel 42
Alexander Markarian 43
Chad Newman 43
Antoinette Catchpole 44
Eddie Chang 44
Bela Dancs 45
Marta Havel 45
Sylvie Schwartz 45
Audry Ropeburn 50
Carmen Velasquez 50
Ben Biri 3131
select weberm('5/20/31/43') from dual;
WEBERM('5/20/31/43')
--------------------
5
select weberm('5/3131') from dual;
WEBERM('5/3131')
----------------
2
So, I believe that the code is still tighter, simpler, cleaner, and now, generalised for use with any-length values (and still, as before, able to handle either Alpha or Numeric values), provided the user follows the consistent-delimiter specifications.
(If one wished to generalise even further, to accommodate the use of
any delimiter for
any invocation of the function, then one could add another argument to the "weberm" function to specify the delimiter of choice.)
Let me know if this passes muster.
![[santa] [santa] [santa]](/data/assets/smilies/santa.gif)
Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services:
www.dasages.com]