Labels

2017 absence absence management Account accounting AIM aliases AME and API application application utilities lookups approval assignments ATO Australia Bank bi publisher budget business business groups CAGR candidates cartisian product case CEMLI Center Stage channels Classification competency concurrent Configuration configuration profile constants contextualization conversion correction cost costing coverage area customization data database date DateTracked deductions define design develop DFF diagnostics document earnings ebs EIT Element employee enhancements erp excel expression extension failure Fastformula FBT Flexfield FND fndload foreign key forms Formula fringe benefit FRM-40654 from FTE Functions fund fusion GL global transfer grade help hierarchy HR HRMS human resource management system implementation income information interfaces internet interview job join key flexfield KFF KPI language learning leave legal employer legislation links lists localization location management New Year obia obiee OLF onboarding oracle oracle applications oracle descriptive flex field oracle descriptive flexfield oracle ebs oracle erp oracle fusion HCM oracle hrms oracle hrms interview questions oracle hrms modules oracle hrms modules list oracle hrms organization oracle hrms table oracle hrms tables oracle hrms tutorial oracle irecruitment oracle legal entities oracle lookups oracle organization hierarchy oracle payroll oracle payroll interview questions oracle payroll tables oracle self service order by Organization organization type otbi package package body package specification patch payg Payment payroll people group perform person personalisation phase pl/sql position primary key process profile programs project qualifier Query question questions Recruiting Center Recruitment regex regular expression reporting tool reports requests requirement requisition resume retropay RICE salary schema security profile select SIT smartorg sql statutory stores STP Super Superannuation system systems Table Taleo taleo 15B Taleo Recruitment tax termination test testing trunc update user group user management user type value set variables View Views Web ADI webadi where work relationship

Thursday, 13 July 2017

Using Regular Expression function (Regex) in SQL - Part IV


  • Consecutive Occurrences

Let’s try to search for two consecutive occurrences of letters from a-z in the following example.

select regexp_substr('Australiaaaaaa','([a-z])\1', 1,1,'i') from dual;
Output: aa
([a-z]) character set a-z
\1 consecutive occurrence of any character in the class [a-z]
1 starting from 1st character in the string
1 First occurrence
i stands for case insensitive

Now let’s try to search for three consecutive occurrences digits from 6 to 9 in the following example.

select case when regexp_like('Patch 10999 applied' ,'([6-9])\1\1') then 'Match Found' else 'No Match Found' end as output from dual;
Output: Match Found
Note: Here we didn’t specify the position or occurrence in the above sql query. By default, oracle will search for the first occurrence of the matching pattern from the start of the string and will stop when it encounters appropriate match. If no match is found it stops at the end of the string

  • Formatting Strings

select regexp_replace('04099661234', '([[:digit:]]{2})([[:digit:]]{4})([[:digit:]]{5})', '(\1) \2-\3') as Formatted_Phone from dual;
Output: (040) 9966-1234
We tried to format a phone number in the above example. Let’s understand the match pattern and replacing string.
Our match pattern is ^([[:digit:]]{3})([[:digit:]]{4})([[:digit:]]{4})$
([[:digit:]]{2}) 2 digits
([[:digit:]]{4}) followed by 4 digits
([[:digit:]]{5}) followed by 5 digits
Why did I group the digits into sub expressions using the parentheses? I could have simply searched for [[:digit:]]{11} as my input string comprises of 11 digits only. To understand the reason, let’s look at the replacing string (\1) \2-\3
( includes a opening parenthesis
\1 represents the first sub group expression in our match pattern i.e. 04
) includes a closing parenthesis
\2 represents the second sub group i.e. 0996
- includes a hyphen
\3 represents the third sub group i.e. 61234

We’ll see some more formatting in our next example.
select regexp_replace('04099661234', '^([[:digit:]]{1})([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{4})$', '+91-\2-\3-\4') as Formatted_Phone from dual;
Output: +91-409-966-1234

In the next example, we shall include a space between every character.
select regexp_replace('ORACLE', '(.)', '\1 ') as Output from dual;
Output: O R A C L E
Some more patterns
In the below example let’s look for http:// followed by a substring of one or more alphanumeric characters and optionally, a period (.)
select regexp_substr('Go to http://www.oracle.com/products and click on database','http://([[:alnum:]]+\.?){3,4}/?') result from dual;
Output: http://www.oracle.com/select regexp_substr('Go to http://www.oracle.co.uk/products and click on database','http://([[:alnum:]]+\.?){3,4}/?') result from dual;
Output: http://www.oracle.co.uk/
http:// The characters http://
([[:alnum:]]+\.?) represents the sub expression: one or more occurrences of alphanumeric characters followed by a dot optionally
{3,4} minimum 3 occurrences of the above sub expression to a max of 4
/? followed by forward slash optionally

Let’s now try to extract the third value from a csv string.
select regexp_substr('1234,New Delhi,India,1.5.105','[^,]+',1,3)as Output from dual;

Output: India
[^,]+ one or more occurrences of non comma characters
1 specifies the starting position
3 Third match

No comments:

Post a Comment