Oracle 11g get all matched occurrences by a regular expression

by

REGEXP_SUBSTR only returns one value. You could turn your string into a pseudo-table and then query that for matches. There’s an XML-based way of doing this that escapes me at the moment, but using connect-by works, as long as you only have one source string:

SELECT REGEXP_SUBSTR(str, '[^ ]+', 1, LEVEL) AS substr
FROM (
    SELECT 'Txa233141b Ta233141 Ta233142 Ta233147 Ta233148' AS str FROM DUAL
)
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(str, '[^ ]+')) + 1;

… gives you:

SUBSTR             
--------------------
Txa233141b           
Ta233141             
Ta233142             
Ta233147            
Ta233148

… and you can filter that with a slightly simpler version of your original pattern:

SELECT substr
FROM (
    SELECT REGEXP_SUBSTR(str, '[^ ]+', 1, LEVEL) AS substr
    FROM (
        SELECT 'Txa233141b Ta233141 Ta233142 Ta233147 Ta233148' AS str
        FROM DUAL
    )
    CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(str, '[^ ]+')) + 1
)
WHERE REGEXP_LIKE(substr, '^[A-Za-z]{2}[0-9]{5,}$');

SUBSTR             
--------------------
Ta233141             
Ta233142             
Ta233147             
Ta233148

Which isn’t very pretty, but neither is holding multiple values in one field.

More Related Contents:

  1. Oracle REGEXP_LIKE and word boundaries
  2. Finding and removing Non-ASCII characters from an Oracle Varchar2
  3. How does the () and [] come into play with regex?
  4. Regular Expression for ” – ” and characters after [closed]
  5. How do (*SKIP) or (*F) work on regex?
  6. Regular Expression to find a string included between two characters while EXCLUDING the delimiters
  7. Difference between * and + regex
  8. Regex to validate JSON
  9. Regex to match only letters
  10. How to calculate the number of occurrence of a given character in each row of a column of strings?
  11. Regular expression for a string that does not start with a sequence
  12. How can I use a variable in the replacement side of the Perl substitution operator?
  13. Why doesn’t `\d` work in regular expressions in sed? [duplicate]
  14. Regular expression for youtube links
  15. How can I use ‘Not Like’ operator in MongoDB
  16. Negate characters in Regular Expression [closed]
  17. Zero-Length regexes and infinite matches?
  18. Regex pattern for HH:MM:SS time string
  19. RegEx pattern any two letters followed by six numbers
  20. How to escape comma and double quote at same time for CSV file?
  21. Match Question Mark in mod_rewrite rule regex
  22. Dollar sign in regular expression and new line character
  23. How to use RegEx in Dart?
  24. Regex for youtube URL
  25. What do \S, \W, \D stand for in regex?
  26. Replace all whitespace with a line break/paragraph mark to make a word list
  27. Why do regex engines allow / automatically attempt matching at the end of the input string?
  28. How do I turn any regex into an complement of itself without complex hand editing?
  29. Apache mod_rewrite REDIRECT_STATUS condition causing directory listing
  30. Matching url with array list of words in AngularJS ui-router

Leave a Comment