Let's take a look at the 2nd argument of this REGEXP function: '^[0-9]+$'. Translated it would mean: start at the beginning of the string, check if there's one or more characters in the range between '0' and '9' (also called a matching character list) until the end of this string. "^", "[", "]", "+", "$" are all Metacharacters.
WITH t AS (SELECT '456' col1 FROM dual UNION SELECT '123x' FROM dual UNION SELECT 'x123' FROM dual UNION SELECT 'y' FROM dual UNION SELECT '+789' FROM dual UNION SELECT '-789' FROM dual UNION SELECT '159-' FROM dual UNION SELECT '-1-' FROM dual ) SELECT t.col1 FROM t WHERE NOT REGEXP_LIKE(t.col1, '^[0-9]+$') ;
Addendum: Here I have to use both "?" and "*" to make sure, that I can have 0 to many digits after the decimal point, but only 0 to 1 occurrence of this substrings. Otherwise, strings like "220.127.116.11" would be possible, if I would write it like this:
Some of you now might say: Hey, what about signed decimal numbers? You could of course combine all the ideas so far and you will end up with a very long and almost unreadable search pattern, or you start combining several regular expression functions. Think about it: Why put all the search patterns into one function? Why not split those into several steps like "check for a valid decimal" and "check for sign".
From this select, the only rows I need to find are those with the column values "." and "-1.1-". I'll start this with a check for valid signs. Since I want to combine this with the check for valid decimals, I'll first try to extract a substring with valid signs through the REGEXP_SUBSTR function:
WITH t AS (SELECT '0' col1 FROM dual UNION SELECT '0.' FROM dual UNION SELECT '.0' FROM dual UNION SELECT '0.0' FROM dual UNION SELECT '-1.0' FROM dual UNION SELECT '.1-' FROM dual UNION SELECT '.' FROM dual UNION SELECT '-1.1-' FROM dual ) SELECT t.* FROM t ;
Remember the OR operator and the matching character collections? But several "^"? Some of the meta characters inside a search pattern can have different meanings, depending on their positions and combination with other meta characters. In this case, the pattern translates into: from the beginning of the string search for "+" or "-" followed by at least another character that is not "+" or "-". The second pattern after the "|" OR operator does the same for a sign at the end of the string.
NVL(REGEXP_SUBSTR(t.col1, '^([+-]?[^+-]+|[^+-]+[+-]?)$'), ' ')
Now the optional sign checks in the REGEXP_LIKE argument can be added to both ends, since the SUBSTR won't allow any string with signs on both ends. Thinking in regular expression again.
WHERE NOT REGEXP_LIKE(NVL(REGEXP_SUBSTR(t.col1, '^([+-]?[^+-]+|[^+-]+[+-]?)$'), ' '), '^[+-]?(\.[0-9]+|[0-9]+(\.[0-9]*)?)[+-]?$' )