Oracle Print Again With Different Substring

171/479

---

REGEXP_SUBSTR

Syntax

Description of the illustration regexp_substr.gif

Purpose

REGEXP_SUBSTR extends the functionality of the SUBSTR function past letting you search a string for a regular expression design. Information technology is also similar to REGEXP_INSTR, but instead of returning the position of the substring, it returns the substring itself. This part is useful if you need the contents of a friction match string but not its position in the source string. The office returns the cord as VARCHAR2 or CLOB data in the same grapheme set as source_char .

This function complies with the POSIX regular expression standard and the Unicode Regular Expression Guidelines. For more data, delight refer to Appendix C, "Oracle Regular Expression Support".

• source_char is a character expression that serves as the search value. It is unremarkably a character column and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

• blueprint is the regular expression. Information technology is normally a text literal and can exist of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. Information technology can contain up to 512 bytes. If the datatype of pattern is different from the datatype of source_char , Oracle Database converts design to the datatype of source_char . For a listing of the operators you lot can specify in pattern , delight refer to Appendix C, "Oracle Regular Expression Support".

• position is a positive integer indicating the grapheme of source_char where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the commencement character of source_char .

• occurrence is a positive integer indicating which occurrence of pattern in source_char Oracle should search for. The default is 1, pregnant that Oracle searches for the first occurrence of pattern .

• match_parameter is a text literal that lets you change the default matching beliefs of the function. Yous can specify one or more of the following values for match_parameter :

  • 'i' specifies instance-insensitive matching.

  • 'c' specifies case-sensitive matching.

  • 'n' allows the flow (.), which is the match-whatsoever-character character, to lucifer the newline character. If y'all omit this parameter, the menstruation does not match the newline grapheme.

  • '1000' treats the source string every bit multiple lines. Oracle interprets ^ and $ as the start and finish, respectively, of whatever line anywhere in the source string, rather than just at the first or end of the entire source string. If you omit this parameter, Oracle treats the source string as a unmarried line.

  • 'x' ignores whitespace characters. Past default, whitespace characters friction match themselves.

  If you specify multiple contradictory values, Oracle uses the last value. For case, if you specify 'ic', and then Oracle uses case-sensitive matching. If you lot specify a character other than those shown above, then Oracle returns an fault.

  If yous omit match_parameter , and then:

  • The default case sensitivity is adamant by the value of the NLS_SORT parameter.

  • A menstruation (.) does non match the newline grapheme.

  • The source string is treated as a single line.

Examples

The following example examines the string, looking for the first substring bounded by commas. Oracle Database searches for a comma followed past one or more occurrences of non-comma characters followed by a comma. Oracle returns the substring, including the leading and trailing commas.

SELECT   REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA',                 ',[^,]+,') "REGEXPR_SUBSTR"   FROM DUAL;  REGEXPR_SUBSTR ----------------- , Redwood Shores,          

The following case examines the string, looking for http:// followed by a substring of i or more alphanumeric characters and optionally, a period (.). Oracle searches for a minimum of iii and a maximum of four occurrences of this substring between http:// and either a slash (/) or the end of the string.

SELECT   REGEXP_SUBSTR('http://www.oracle.com/products',                 'http://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR"   FROM DUAL;  REGEXP_SUBSTR ---------------------- http://www.oracle.com/          

brehmertorrisheacer.blogspot.com

Source: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions131.htm