Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
9 mars 2010 2 09 /03 /mars /2010 17:24

Recently, i've read an article from Steven Feuerstein about stripping strings :
http://www.oracle.com/technology/oramag/oracle/06-jan/o16plsql.html

Starting with Oracle 10g, you can use REGEXP_REPLACE to strip any string from a list of given chars.
However, in earlier version, he illustrates the use of the TRANSLATE function for this purpose.

The issues can be summed up as :
- Passing a NULL second argument to the translate function nullyfies the result :

SELECT translate('Steven Feuerstein', 'x', '') FROM dual;
T
-
 
1 row selected.

- The trick : use a joker char which you replace by... itself

SELECT translate('Steven Feuerstein', 'xe', 'x') FROM dual
TRANSLATE('S
------------
Stvn Furstin

1 row selected.

... but you cannot use a constant joker character for all of you strippings, because this character might be the one you want to strip off !
That's why Uncle Steven suggest you should have this joker char beeing as well an argument of your stripping function.

Today in this article, i'll just show a funny way to do it without joker char argument ;)
Disclaimer : i don't want to compete with master Steven, just having a little fun with Oracle SQL !


1) CONNECT BY LEVEL forever !

Let's use the CONNECT BY LEVEL bug to "spell" the string in rows :

SELECT substr('Steven Feuerstein', level, 1) as c, level as rk
FROM dual
CONNECT BY level <= length('Steven Feuerstein')

C         RK
- ----------
S          1
t          2
e          3
v          4
e          5
n          6
           7
F          8
e          9
u         10
e         11
r         12
s         13
t         14
e         15
i         16
n         17

17 rows selected.

Now the stripping just looks like a filter :

SELECT c, row_number() over(order by rk) rk from
    (SELECT substr('Steven Feuerstein', level, 1) as c, level as rk
    FROM dual
    CONNECT by level <= length('Steven Feuerstein')) inp
 WHERE inp.c <> 'e'

C         RK
- ----------
S          1
t          2
v          3
n          4
           5
F          6
u          7
r          8
s          9
t         10
i         11
n         12


12 rows selected.

2) Re-aggregate it... using replace ?!

You sure noticed the ranking of the lines in the previous query. The purspose was obvious : beeing able to re-aggregate the chars after filtering !

In Oracle 11g version, the build in function LISTAGG would make it quite easy.

I choose the XMLAGG method that works in 9i :
- you put your string in single XML tags
- you aggregate the tags...
... then you strip the tags from the resulting string !


Really funny, was there really an issue from the beginning ?


SELECT replace('Steven Feuerstein', 'e', '') as A, translate('Steven Feuerstein', 'e', '') AS b
FROM dual;

REPLACE('STE T
------------ -
Stvn Furstin 


1 row selected.


Yeah, just as expected !
But you can only give one character to strip (unless you want to strip it from a string, which differs from stripping several different chars) as argument of the REPLACE function.


Let's change our example to prove the worthiness of our trick and strip the input string from two chars : "e" and "t"
... and re-aggregate the result :


SELECT replace(replace(xmlagg(xmlelement("marcel", c) order by rk), '<marcel>', ''), '</marcel>', '')
FROM
(SELECT c, row_number() over(order by rk) rk from
    (SELECT substr('Steven Feuerstein', level, 1) as c, level as rk
    FROM dual
    CONNECT by level <= length('Steven Feuerstein')) inp
 WHERE inp.c NOT IN (SELECT substr('et', level, 1) FROM dual CONNECT BY level <= length('et'))
 )

REPLACE(REPLACE(XMLAGG(XMLELEMENT("MARCEL",C)ORDERBYRK),'<MARCEL>',''),'</MARCEL
--------------------------------------------------------------------------------
Svn Fursin                                        
                             

1 row selected.

Great...
...But if you really want to use this underperforming method that could become obsolete whenever the CONNECT BY LEVEL bug would be fixed, you have to put it in a function !
Because you cannot "pass" arguments to the deeper levels of a nested query expression.


Cosmetic SQL Rules !


 

Partager cet article

Published by Pacman - dans SQL cosmétique
commenter cet article

commentaires