Oracle Character Function SUBSTR

1. The Oracle `SUBSTR` function extracts a substring from a given string, starting at a specified position and extending for a given number of characters. The command provided:
   

   SELECT SUBSTR('PANDABEAR', 3, 4) "Substring"
   FROM DUAL;
   

   
   breaks down as follows:
   1. `'PANDABEAR'` is the string from which the substring is to be extracted.
   2. `3` is the starting position, indicating that the extraction should begin from the third character of the string, which is `N`.
   3. `4` specifies the length of the substring to be extracted, counting from the starting position.
   
   Therefore, starting from the third character `N`, and extracting four characters from this point, yields `NDAB`. The output of the provided SQL command will be a single column with the header "Substring" and a single row containing the substring `NDAB`:
   

   Substring
   ---------
   NDAB
   

The substring function, which returns part of the column, is written like as follows:

SUBSTR(col1, start, length)

You can combine the substring function with the instring function and create a useful tool. Here is an example, using the Pet Store schema. Let us say you want to retrieve only the first sentence from each Pet Log entry. You can use the substring function to return a portion of the data, but the exact length of the sentence varies with each entry. By using the instring function, you can locate the first period (.) found in the data. This is where the sentence ends. Add one to this number, and you can retrieve the complete sentence, including the period. The diagram below shows the SQL query that does it.

The first command you see in the figure is a SQL*Plus column formatting command. It lets me show you the entire contents of the column in a narrow window. See the module titled "The Oracle SQL*Plus environment" for more information on SQL*Plus commands. In the next lesson, you will learn use of the character functions UPPER, INITCAP, RTRIM, and SOUNDEX.

Oracle does not support the `LEFT` and `RIGHT` functions directly. Instead, you can achieve the same results using the `SUBSTR` function. Here’s how you can use the `SUBSTR` function in Oracle to replicate the functionality of `LEFT` and `RIGHT`:
LEFT Function Equivalent in Oracle The `LEFT` function in SQL Server or MySQL extracts a specified number of characters from the beginning (left side) of a string. In Oracle, you can use `SUBSTR` to achieve the same result.
SQL Server/MySQL Syntax:

SELECT LEFT(column_name, number_of_characters) FROM table_name;

Oracle Equivalent:

SELECT SUBSTR(column_name, 1, number_of_characters) FROM table_name;

Example:

-- SQL Server/MySQL
SELECT LEFT('Hello World', 5) FROM dual;

-- Oracle
SELECT SUBSTR('Hello World', 1, 5) FROM dual;

RIGHT Function Equivalent in Oracle
The `RIGHT` function in SQL Server or MySQL extracts a specified number of characters from the end (right side) of a string. In Oracle, you can use `SUBSTR` combined with `LENGTH` to achieve the same result.
SQL Server/MySQL Syntax:

SELECT RIGHT(column_name, number_of_characters) FROM table_name;

Oracle Equivalent:

SELECT SUBSTR(column_name, -number_of_characters) FROM table_name;

Alternatively:

SELECT SUBSTR(column_name, LENGTH(column_name) - number_of_characters + 1, number_of_characters) FROM table_name;

Example:

-- SQL Server/MySQL
SELECT RIGHT('Hello World', 5) FROM dual;

-- Oracle
SELECT SUBSTR('Hello World', -5) FROM dual;

-- Alternative in Oracle
SELECT SUBSTR('Hello World', LENGTH('Hello World') - 5 + 1, 5) FROM dual;

Explanation

1. SUBSTR Function: The `SUBSTR` function in Oracle extracts a substring from a string. Its syntax is `SUBSTR(string, start_position, length)`.
   • `string`: The original string.
   • `start_position`: The position to start extracting (1-based index). Negative values count from the end of the string.
   • `length`: The number of characters to extract.
2. LENGTH Function: The `LENGTH` function returns the length of a string.

Using `SUBSTR` with these parameters allows you to effectively mimic the behavior of `LEFT` and `RIGHT` functions in Oracle.