Born to Thrive

Examine the data in the DOCNO column of the DOC_DETAILS table:


DOCNO
123-456-7890
233-67-90876
45-789-23456


You need to extract the digits between the hyphens as follows:


SUBSTR
456
67
789


Which SQL statement gives the required result?
A. SELECT REGEXP_SUBSTR(docno,'-[^-]+') "SUBSTR" FROM doc_details;
B. SELECT REGEXP_SUBSTR(docno,'^-[^-]+-')"SUBSTR" FROM doc_details;
C. SELECT REGEXP_SUBSTR(docno,'-[^-]+',2) "SUBSTR" FROM doc_details;
D. SELECT REGEXP_SUBSTR(docno, '[^-]+',1,2) "SUBSTR" FROM doc_details;

Theory:

Since the question contains queries with REGEXP_SUBSTR function and regular expression syntax, so the information required to answer it can found as follows: REGEXP_SUBSTR and REGULAR EXPRESSION SYNTAX.

REGEXP_SUBSTR extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern. This function is useful if you need the contents of a match string but not its position in the source string. The function returns the string as VARCHAR2 or CLOB data in the same character set as source_string.

• source_string is a character expression that serves as the search value. It is commonly a character column and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
• pattern is the regular expression. It is usually a text literal and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes.
• position is a positive integer indicating the character of source_string where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_string.
• occurrence is a positive integer indicating which occurrence of pattern in source_string Oracle should search for. The default is 1, meaning that Oracle searches for the first occurrence of pattern.
• match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more value (ie. 'i' specifies case-insensitive matching)

Below are just few regular expression operators and metdasymbols - click on the link above to see the complete list of regular expression symbols

• "+" - matches one or more occurrences
• "^" - matches the beginning-of-line character. By default it matches only the beginning of an entire string.
• "$" - matches the end-of-line character. By default it matches only the end of an entire string.
• "[]" - specify a matching list that should match any one of the expressions represented in the list. A nonmatching list expression begins with a circumflex (^) and specifies a list that matches any character except for the expressions represented in the list.

Answer:

A - Wrong - the query finds a string that starts with a dash character and that has no other dash character, which would be numbers in the middle of the docno, so the query displays: -456, -67, -789 (dash character is part of the match).
B - Wrong - the search patterns looks for a string that doesn't start with a dash character and that has no dash character, which would be numbers at the beginning of the docno, so the query displays: 123, 233, 45
C - Wrong - the query has the same search pattern as the query from the answer A, except it starts looking from the 2nd character. As a result, it produces the same results as query from the answer A: -456, -67, -789
D - Correct - the search pattern looks for strings that don't have a dash character, so all numbers from the docno match the pattern, but the query takes the second group from the match, so the query displays:  456, 67, 789