A typical scenario that involves splitting a comma-separated string into rows:
- in a UI-driven query, the user would enter a list of IDs into an input box, and
- the application should retrieve details for the input list of IDs
The count of IDs in the list can vary — the user may enter a single value or multiple, and the underlying SQL should fetch the details for all of them.
This sounds simple enough on the face of it: bind a parameter to a comma-separated list of values such as ‘A1,A2,A4’ and then look for corresponding rows using SQL for those IDs.
There’s a catch though.
[click to continue…]
Oracle has some neat ways of handling hierarchical data.
A single Oracle SQL can find the entire tree of data above or below a node in a hierarchy using the CONNECT BY clause.
What if the requirement is to flatten hierarchical data?
For example, given a table containing the employee reporting hierarchy of an organization (image alongside), get a single SQL to return the 4 types of employee roles as columns:
- PROJECT_MANAGER (Level 1)
- DBA (Level 2)
- TEAM_LEAD (Level 2)
This is trickier than transposing rows as columns in a non-hierarchical table, and needs a little extra to get the result. But it is nowhere near as tough as it appears. No multiple self-joins, no recursive CTE.
This post will show you how to flatten hierarchical data using a single SQL in Oracle.
[click to continue…]
In applications that take user email id as input, there is a need to check for email id validity. Here is a very easy validation for syntax of an email address, using regular expressions in Oracle SQL.
The basic email address format is username@example.com. The SQL will verify that the email address provided fits into that format. This can be used before data entry, or coded on a table as a check constraint.
[click to continue…]
What is a regular expression?
A regular expression (also called regex or regexp for short) is a sequence of characters that describes a pattern in text.
Some examples of regular expressions:
p..t => A dot stands for a single character. This regular expression will match words that start with a ‘p’, end with a ‘t’ and have any two characters in between them (since there are two dots within).
[click to continue…]
