This package is a brute force string generator using all possible combinations of a characters in a string up to a maximum length. Together with the regular expressions, I can now show what combinations my solution would allow to pass. But see for yourself:
CREATE OR REPLACE PACKAGE regex_utils AS -- Regular Expression Utilities -- Version 0.1 TYPE t_outrec IS RECORD( data VARCHAR2(255) ); TYPE t_outtab IS TABLE OF t_outrec; FUNCTION gen_data( p_charset IN VARCHAR2 -- character set that is used for generation , p_length IN NUMBER -- length of the generated ) RETURN t_outtab PIPELINED; END regex_utils; / CREATE OR REPLACE PACKAGE BODY regex_utils AS -- FUNCTION gen_data returns a collection of generated varchar2 elements FUNCTION gen_data( p_charset IN VARCHAR2 -- character set that is used for generation , p_length IN NUMBER -- length of the generated ) RETURN t_outtab PIPELINED IS TYPE t_counter IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER; -- v_counter t_counter; v_exit BOOLEAN; v_string VARCHAR2(255); v_outrec t_outrec; BEGIN FOR max_length IN 1..p_length LOOP -- init counter loop FOR i IN 1..max_length LOOP v_counter(i) := 1; END LOOP; -- -- start data generation loop -- v_exit := FALSE; -- WHILE NOT v_exit LOOP -- start generation v_string := ''; -- FOR i IN 1..max_length LOOP v_string := v_string || SUBSTR(p_charset, v_counter(i), 1); END LOOP; -- -- set outgoing record -- v_outrec.data := v_string; -- -- now pipe the result -- PIPE ROW(v_outrec); -- -- increment loop -- <<inc_loop>> FOR i IN REVERSE 1..max_length LOOP v_counter(i) := v_counter(i) + 1; IF v_counter(i) > LENGTH(p_charset) THEN IF i > 1 THEN v_counter(i) := 1; ELSE v_exit := TRUE; END IF; ELSE -- no further processing required EXIT inc_loop; END IF; END LOOP; -- END LOOP; END LOOP; END gen_data; END regex_utils; /
You will see some results, which are perfectly valid for my definition of decimal numbers but haven't been mentioned, like '000' or '+.00'. From now on I will also use this package to verify the solutions I'll present to you and hopefully reduce my share of typos.
SELECT * FROM (SELECT data col1 FROM TABLE(regex_utils.gen_data('+-.0', 5)) ) t WHERE REGEXP_LIKE(NVL(REGEXP_SUBSTR(t.col1, '^([+-]?[^+-]+|[^+-]+[+-]?)$' ), ' ' ), '^[+-]?(\.[0-9]+|[0-9]+(\.[0-9]*)?)[+-]?$' ) ;
No surprise there. I'm replacing all characters except spaces with a null string. Since REGEXP_REPLACE assumes a NULL string as replacement argument, I can save on adding a third argument, which would look like this:
SELECT NVL(LENGTH(REGEXP_REPLACE('Having fun with regular expressions', '[^ ]')), 0) FROM dual ;
So REPLACE will return all the spaces which we can count with the LENGTH function. If there aren't any, I will get a NULL string, which is checked by the NVL function. If you want you can play around by changing the space character to somethin else.
REGEXP_REPLACE('Having fun with regular expressions', '[^ ]', '')
You may have noticed that I changed from using the "^" as a NOT operator to using the "|" OR operator and the "." any character placeholder. This neat little trick allows to filter all other characters except the one we're looking in the first place. "\1" as backreference is outside of our subexpression since I don't want to count the trailing spaces and is used both in the search pattern and the replacement argument.
SELECT NVL(LENGTH(REGEXP_REPLACE('Having fun with regular expressions', '( )\1*|.', '\1'))) FROM dual ;
This time I don't use a backreference, the "+" operator (remember? 1 or more) will suffice. And since I want to count the occurences, not the letters, I moved the "+" meta character outside of the subexpression. The "|." trick again proved to be useful.
SELECT NVL(LENGTH(REGEXP_REPLACE('Having fun with regular expressions. !', '([a-z])+|.', '\1', 1, 0, 'i')), 0) FROM dual;
Now regular expressions are powerful, but there is no parameter that allows us to reverse the search direction. However, remembering that we have the "$" meta character that means (until the) end of string, all I have to do is use a search pattern that looks for a combination of space and non-space characters including the end of a string. Now compare the REGEXP_INSTR function to the previous solution:
WITH t AS (SELECT 'Where is the last space?' col1 FROM dual) SELECT INSTR(col1, ' ', -1) FROM DUAL;
So in this case, it'll remain a matter of taste what you want to use. If the search pattern has to look for the last occurrence of another regular expression, this is the way to solve such a requirement.
SELECT REGEXP_INSTR(t.col1, ' [^ ]*$') FROM t;
What about middle names, for example 'John J. Doe'? Look for yourself, it still works.
SELECT REGEXP_REPLACE('John Doe', '^(.*) (.*)$', '\2, \1') FROM dual ;
After what you've learned so far, that wasn't too hard, was it? Enough for now ...
SELECT REGEXP_REPLACE('10~20~30~40~50', '^(.*)~(.*)~(.*)~(.*)~(.*)$', '\5~\4~\3~\2~\1' ) FROM dual;
One german typo :-)I received a functional spec from my Dutch analyst in which it is writtenI'm replacing all characters except spaces mit anull string.
tnsnames voor EDWH:Had to look for translators.
PCESCRD1 = (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
db user: BW_I2_VIEWER / BW_I2_VIEWER_SCRD1
WITH t AS (SELECT 'Where is the last space?' col1could actually be simplified to this...right?
SELECT LENGTH(t.col1) - DECODE(INSTR(REVERSE(t.col1), REVERSE(' ')),
INSTR(REVERSE(t.col1), REVERSE(' ')) - 1)
select instr('Where is the last space?',' ',-1) from dual;
After what you've learned so far, that wasn't too hard, was it? Enough for now ...remembering how hard i was to understand regular expression in my college time, yes that wasn't so hard.