REGEXP_SUBSTR extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern. It is also similar to REGEXP_INSTR, but instead of returning the position of the substring, it returns the substring itself. This function is useful if you need the contents of a match string but not its position in the source string.
position is a positive integer indicating the character of source_char where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_char.
The following example parse the last name and first from the full name string:
regexp_substr('Bond,Jame', '[^,]+', 1, 1) last_name, -- first occurrence of non-comma substring
regexp_substr('Bond,Jame', '[^,]+', 1, 2) first_name -- second occurrence of non-comma substring
The following example examines the string, looking for http:// followed by a substring of one or more alphanumeric characters and optionally, a period (.). Oracle searches for a minimum of two occurrences of this substring between http:// and either a slash (/) or the end of the string.
Select the second substring of letters that ends with "thy."
SELECT REGEXP_SUBSTR('healthy, wealthy, and wise','\w+thy',1,2);
© 2020 Digcode.com. All rights reserved.