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
Sunday 30 July 2017
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
([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.
Using Regular Expression function (Regex) in SQL - Part III
- Search and replace white spaces
select regexp_replace('Each One Teach One','[[:blank:]]{2,8}',' ') from dual;
Output: Each One Teach One
In the above example we have replaced multiple white spaces (2 to 8) in the source string with single space.
Let’s now try to understand the pattern [[:blank:]]{2,8}
Now that you are familiar with the patterns you may be wondering about the missing (^$) anchors in the pattern. These have been skipped purposefully. In the source string we wanted to search for multiple spaces anywhere in the string. So we need not specify the start (^) and end ($) anchors.
Note: ^[[:blank:]]{2,8}$ would mean a pattern consisting of min of 2 spaces to a max of 8 and no other
characters and we won’t find a match for the above input.
- Validate email
Using Regular Expression function (Regex) in SQL - Part II
- Validate a string for lower case alphabets only
select case when regexp_like('Mohit' ,'^([[:lower:]]{3,12})$') then 'Match Found' else 'No Match Found' end as output from dual;
Output: No Match Found
If we change the input to “mohit”, we get a match. In the above example we tried to check if the source string contains only lower case alphabets with a string length varying from minimum of 3 to max of 12.
The pattern is similar as above but {3,12}. {3,12} specifies at least 3 alphabets but not more than 12
Output: No Match Found
If we change the input to “mohit”, we get a match. In the above example we tried to check if the source string contains only lower case alphabets with a string length varying from minimum of 3 to max of 12.
The pattern is similar as above but {3,12}. {3,12} specifies at least 3 alphabets but not more than 12
- Case Sensitive Search
Using Regular Expression function (Regex) in SQL - Part I
Everyday most of us deal with multiple string functions in Sql. May it be for truncating a string, searching for a substring or locating the presence of special characters.
The regexp functions available in Oracle can help us achieve the above tasks in
a simpler and faster way. I have tried to illustrate the behavior of the regexp
functions with few illustrations below. The patterns can be used with any of
the regular expression functions.
- Validate a string for alphabets only
select case when regexp_like('Oracle'
,'^[[:alpha:]]{6}$') then 'Match Found' else 'No Match Found' end as output
from dual;
Subscribe to:
Posts (Atom)