Wednesday, August 26, 2015

How to get nth sting from comma delimiter string or How to USE REGEXP_SUBSTR in Oracle SQL PLSQL



To get nth record from Comma Delimiter String.

SELECT REGEXP_SUBSTR ('ABC,BDB,wew,sds,ety,thyrty', '[^,]+',  1,   4)    aa FROM DUAL;


4th string: sds is the output.


To get nth record from Pipe Delimiter String.

SELECT REGEXP_SUBSTR ('ABC|BDB|wew|sds|ety|thyrty', '[^|]+',  1,   4)    aa FROM DUAL;


4th string: sds is the output.


http://www.techonthenet.com/oracle/functions/regexp_substr.php

ORACLE/PLSQL: REGEXP_SUBSTR FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL REGEXP_SUBSTR function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL REGEXP_SUBSTR function is an extension of the SUBSTR function. This function, introduced in Oracle 11g, will allow you to extract a substring from a string using regular expression pattern matching.

SYNTAX

The syntax for the REGEXP_SUBSTR function in Oracle is:
REGEXP_SUBSTR( string, pattern [, start_position [,  nth_appearance [, match_parameter [, sub_expression ] ] ] ] ] )

Parameters or Arguments

string
The string to search. It can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
pattern
The regular expression matching information. It can be a combination of the following:
ValueDescription
^Matches the beginning of a string. If used with a match_parameter of 'm', it matches the start of a line anywhere within expression.
$Matches the end of a string. If used with a match_parameter of 'm', it matches the end of a line anywhere within expression.
*Matches zero or more occurrences.
+Matches one or more occurrences.
?Matches zero or one occurrence.
.Matches any character except NULL.
|Used like an "OR" to specify more than one alternative.
[ ]Used to specify a matching list where you are trying to match any one of the characters in the list.
[^ ]Used to specify a nonmatching list where you are trying to match any character except for the ones in the list.
( )Used to group expressions as a subexpression.
{m}Matches m times.
{m,}Matches at least m times.
{m,n}Matches at least m times, but no more than n times.
\nn is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n.
[..]Matches one collation element that can be more than one character.
[::]Matches character classes.
[==]Matches equivalence classes.
\dMatches a digit character.
\DMatches a nondigit character.
\wMatches a word character.
\WMatches a nonword character.
\sMatches a whitespace character.
\Smatches a non-whitespace character.
\AMatches the beginning of a string or matches at the end of a string before a newline character.
\ZMatches at the end of a string.
*?Matches the preceding pattern zero or more occurrences.
+?Matches the preceding pattern one or more occurrences.
??Matches the preceding pattern zero or one occurrence.
{n}?Matches the preceding pattern n times.
{n,}?Matches the preceding pattern at least n times.
{n,m}?Matches the preceding pattern at least n times, but not more than m times.
start_position
Optional. It is the position in string where the search will start. If omitted, it defaults to 1 which is the first position in the string.
nth_appearance
Optional. It is the nth appearance of pattern in string. If omitted, it defaults to 1 which is the first appearance of pattern in string.
match_parameter
Optional. It allows you to modify the matching behavior for the REGEXP_SUBSTR function. It can be a combination of the following:
ValueDescription
'c'Perform case-sensitive matching.
'i'Perform case-insensitive matching.
'n'Allows the period character (.) to match the newline character. By default, the period is a wildcard.
'm'expression is assumed to have multiple lines, where ^ is the start of a line and $ is the end of a line, regardless of the position of those characters in expression. By default, expression is assumed to be a single line.
'x'Whitespace characters are ignored. By default, whitespace characters are matched like any other character.
subexpression
Optional. This is used when pattern has subexpressions and you wish to indicate which subexpression in pattern is the target. It is an integervalue from 0 to 9 indicating the subexpression to match on in pattern.
Note:
  • If there are conflicting values provided for match_parameter, the REGEXP_SUBSTR function will use the last value.
  • If you omit the match_behavior parameter, the REGEXP_SUBSTR function will use the NLS_SORT parameter to determine if it should use a case-sensitive search, it will assume that string is a single line, and assume the period character to match any character (not the newline character).
  • If the REGEXP_SUBSTR function does not find any occurrence of pattern, it will return NULL.
  • See also the SUBSTR function.

APPLIES TO

The REGEXP_SUBSTR function can be used in the following versions of Oracle/PLSQL:
  • Oracle 12c, Oracle 11g

EXAMPLE - MATCH ON WORDS

Let's start by extracting the first word from a string.
For example:
SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)')
FROM dual;

Result: 'TechOnTheNet '
This example will return 'TechOnTheNet ' because it will extract all non-whitespace characters as specified by (\S*) and then the first whitespace character as specified by (\s). The result will include both the first word as well as the space after the word.
If you didn't want to include the space in the result, we could modify our example as follows:
SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)')
FROM dual;

Result: 'TechOnTheNet'
This example would return 'TechOnTheNet' with no space at the end.
If we wanted to find the second word in the string, we could modify our function as follows:
SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)', 1, 2)
FROM dual;

Result: 'is '
This example would return 'is ' with a space at the end of the string.
If we wanted to find the third word in the string, we could modify our function as follows:
SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)', 1, 3)
FROM dual;

Result: 'a '
This example would return 'a ' with a space at the end of the string.

EXAMPLE - MATCH ON DIGIT CHARACTERS

Let's look next at how we would use the REGEXP_SUBSTR function to match on a single digit character pattern.
For example:
SELECT REGEXP_SUBSTR ('2, 5, and 10 are numbers in this example', '\d')
FROM dual;

Result: 2
This example will extract the first numeric digit from the string as specified by \d. In this case, it will match on the number 2.
We could change our pattern to search for a two-digit number.
For example:
SELECT REGEXP_SUBSTR ('2, 5, and 10 are numbers in this example', '(\d)(\d)')
FROM dual;

Result: 10
This example will extract a number that has two digits side-by-side as specified by (\d)(\d). In this case, it will skip over the 2 and 5 numeric values and return 10.
Now, let's look how we would use the REGEXP_SUBSTR function with a table column and search for a two digit number.
For example:
SELECT REGEXP_SUBSTR (address, '(\d)(\d)')
FROM contacts;
In this example, we are going to extract the first two-digit value from the address field in the contacts table.

EXAMPLE - MATCH ON MORE THAN ONE ALTERNATIVE

The next example that we will look at involves using the | pattern. The | pattern is used like an "OR" to specify more than one alternative.
For example:
SELECT REGEXP_SUBSTR ('Anderson', 'a|e|i|o|u')
FROM dual;

Result: 'e'
This example will return 'e' because it is searching for the first vowel (a, e, i, o, or u) in the string. Since we did not specify a match_parameter value, the REGEXP_SUBSTR function will perform a case-sensitive search which means that the 'A' in 'Anderson' will not be matched.
We could modify our query as follows to perform a case-insensitive search as follows:
SELECT REGEXP_SUBSTR ('Anderson', 'a|e|i|o|u', 1, 1, 'i')
FROM dual;

Result: 'A'
Now because we have provide a match_parameter of 'i', the query will return 'A' as the result. This time, the 'A' in 'Anderson' will be found as a match.
Now, let's quickly show how you would use this function with a column.
So let's say we have a contact table with the following data:
contact_idlast_name
1000Anderson
2000Smith
3000Johnson
Now, let's run the following query:
SELECT contact_id, last_name, REGEXP_SUBSTR (last_name, 'a|e|i|o|u', 1, 1, 'i') AS "First Vowel"
FROM contacts;
These are the results that would be returned by the query:
contact_idlast_nameFirst vowel
1000AndersonA
2000Smithi
3000Johnsono

EXAMPLE - MATCH ON NTH_OCCURRENCE

The next example that we will look at involves the nth_occurrence parameter. The nth_occurrence parameter allows you to select which occurrence of the pattern you wish to extract the substring for.

First Occurrence

Let's look at how to extract the first occurrence of a pattern in a string.
For example:
SELECT REGEXP_SUBSTR ('TechOnTheNet', 'a|e|i|o|u', 1, 1, 'i')
FROM dual;

Result: 'e'
This example will return 'e' because it is extracting the first occurrence of a vowel (a, e, i, o, or u) in the string.

Second Occurrence

Next, we will extract for the second occurrence of a pattern in a string.
For example:
SELECT REGEXP_SUBSTR ('TechOnTheNet', 'a|e|i|o|u', 1, 2, 'i')
FROM dual;

Result: 'O'
This example will return 'O' because it is extracting the second occurrence of a vowel (a, e, i, o, or u) in the string.

Third Occurrence

For example:
SELECT REGEXP_SUBSTR ('TechOnTheNet', 'a|e|i|o|u', 1, 3, 'i')
FROM dual;

Result: 'e'
This example will return 'e' because it is extracting the third occurrence of a vowel (a, e, i, o, or u) in the string.
Share:
While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy.
Copyright © 2003-2015 TechOnTheNet.com. All rights reserved.
Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)