Oracle Regular Expressions. Dangerous Range

An Oracle developer who often uses regular expressions in code sooner or later can face a phenomenon that is indeed mystical. Long-term searches for the root of the problem can lead to weight loss, appetite and provoke various kinds of psychosomatic disorders – all this can be prevented with the help of the regexp_replace function. It can have up to 6 arguments:

REGEXP_REPLACE (

  1. source_string,
  2. template,
  3. substituting_string,
  4. the start position of the match search with a template (default 1),
  5. a position of occurrence of the template in a source string (by default 0 equals all occurrences),
  6. modifier (so far it is a dark horse)

)

(more…)

Introducing Common Table Expressions in SQL Server

Common Table Expressions, or CTE for short, is simply a technique to create a temporary set of records that can be referenced within an INSERT, SELECT, UPDATE or DELETE statement.

Common table expressions were introduced by Microsoft in SQL Server 2005. They are not stored as objects in the database memory as their lifespan is equal to the execution time of the query. As soon as a query completes they are removed from the database memory. CTE can be referenced in a query as many times as you want and they can also be self-referencing.

(more…)

Introduction to Temporary Tables in SQL Server

A temporary table in SQL Server, as the name suggests, is a database table that exists temporarily on the database server. A temporary table stores a subset of data from a normal table for a certain period of time.

Temporary tables are particularly useful when you have a large number of records in a table and you repeatedly need to interact with a small subset of those records. In such cases instead of filtering the data again and again to fetch the subset, you can filter the data once and store it in a temporary table. You can then execute your queries on that temporary table. Temporary tables are stored inside “tempdb” which is a system database. Let’s take a look at how you can use a temporary data in a simple scenario.

(more…)