SQL

Inserting Records Conditionally

I faced a typical problem at work not too long ago dealing with inserting conditional data into a table. Typically I would write a bit of code to handle the conditional logic, but I thought about the problem for a bit and came up with a different solution using SQL. Here is the background of…

Hierarchical Data in SQL Server

Often times in applications, I am required to maintain data that has a hierarchical relationship such as a tree structure. The easiest way to approach this problem is to add a key to the table that refers to the record’s parent key. For example, in my last project, I needed to have a table that…

Using SQL to get the number of occurrences of a character in a string in SQL Server

This SQL Snippet can be used to get the count of a single character, or an entire phrase, in a string.  We have a seperate post for doing the same thing in Oracle. Assume you have the string “a,b,c,d,e” and you want the count of commas (“,”) contained in that string.  Just use the SQL below:…

Select Sequential Numbers with Oracle SQL

Oracle provides the connect by clause that can be used to specify a relationship between parent rows and child rows.  This is used as part of the hierarchical query clause.  In general usage, the connect by clause uses the prior keyword to define the relationship.  When using the connect by clause, the level pseudocolumn is…

Using SQL to get the number of occurances of a character in a string in Oracle

This SQL Snippet can be used to get the count of a single character in a string using Oracle. We have a seperate post for doing the same thing in SQL Server. Assume you have the string a,b,c,d,e and you want the count of commas.  Just use the SQL below. Select length('a,b,c,d,e')-length(replace('a,b,c,d,e',',')) from dual; Running this query returns…

Convert a CSV String into Rows using OracleSQL

The is the Oracle version of an SQL Solution which can transform a string of CSV values into rows, with one row per value.  The SQL snippet uses a delimited string, and will convert this string to rows using the delimiter.  Assume you have a Comma Seperated Value (CSV) string such as: a,b,c,d and need to convert it to…