After SQL Concat function, SQL Substr function is the second character manipulation function in the hierarchy.
As the name suggests SQL Substr function will return a substring from a given source string.
Syntax of SQL SUBSTR ( ) Function
Substr ( source_string, start_pos, Substr_length )
As we can see SQL substr function takes 3 parameters. These three parameters are:
- Source_String: Source string is the string from which you want to extract the segment.
- Starting_Pos: start_pos is the Starting position sub-string from the source string.
- Substr_length: substr_length is the length of the substring.
First two parameters are MANDATORY to specific while the third one is OPTIONAL.
So we can say that SQL SUBSTR () function will return a sub string of a specified length from the source string beginning at a given position.
First parameter which is the source string can be of any data type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB whereas the other two parameters that are start_pos and Substr_length must be of number data type.
The returning result of SQL Substr ( ) function is of the same data type as that of the source string.
Examples of SQL Substr ( ) Function
Let’s see an example of SQL Substr function.
SQL> SELECT substr (‘www.RebellionRider.com’, 5, 14) FROM dual;
Here in the above query the url of my website www.RebellionRider.com is our source string with the total length of 22 characters. I want to extract the name of my website which is “RebellionRider”. So if you count the total length of the name of the website, it is 14. That’s why I have specified 14 as my third parameter of SQL Substr function called substr_length. Also the name of the website RebellionRider is starting from the 5th position therefore I have specified 5 as the second parameter of SQL substr function which is strt_pos or starting position.
On Execution the output of this example will be the name of the Website ‘RebellionRider’.
Scenarios
There are a few scenarios related to SQL Substr function such as –
Scenario 1: When starting position is larger than the length of the source string.
In this case SQL Substr function will return NULL as a result. Let’s do an example.
SELECT substr( ‘www.RebellionRider.com’, 23, 14 ) FROM dual;
As you can see here I have specified 23 as starting position and the total length of our source string is 22 characters. On executing this query we will get NULL in return.
Scenario 2: When the Substr_length is greater than source string
In this case the segment returns the substring from starting position to the end of the String.
For example
SELECT substr( ‘www.RebellionRider.com’, 5, 23 ) FROM dual;
Our starting position is at 5 which is the first R of RebellionRider and length of substring is set to 23 which is greater than the length of source string that is 22.
On executing this query we will get a substring from first R of RebellionRider till the end of the source String.
Scenario 3: When you supply numeric or arithmetic expression or a Data as Source string to SQL Substr function
In this scenario If you have supplied a numeric string instead of character as source string, the oracle engine casts them as a character when they occur as parameter to SQL Substr function. And if you have supplied Arithmetic expression or a DATE then The Oracle engine first solves or evaluates the Arithmetic expression or the DATE & then it casts them as a character.
Means if you have arithmetic expression in your source string then oracle will first solve it and then change or say cast the value of its result into character. Let’s see an example.
SELECT substr( 50000-7, 2, 4 ) FROM dual;
Oracle first evaluates the arithmetic expression that is 50000-7 equal to 49993.And then oracle engine casts this result 49993 into a character string. Means 49993 will be a 5 character string.
Starting position of substring is 2 that means from the first 9 of 49993.
We specified the length of substring as 4 so we must get 9993 as our result.
Similarly if you give a data as your source string it first gets evaluated and then gets casted. Let’s do an example
SELECT Substr ( sysdate, 4, 3) FROM dual;
Above query first evaluates the SYSDATE function and then converts the date returned into a character string. Assume that the current system date is 02-APR-14. The search for the substring begins at position 4 and the three characters from that position onward are extracted, yielding the substring APR.
Scenario 4: When Starting position (start_pos) is set To 0 (Zero)
In this scenario when user sets the starting position which is the second argument of SQL Substr function to 0 (Zero) then the oracle engine treats this zero as One and starts searching or say extracting sub string from the starting of the Source string.
For example
SELECT substr( ‘www.RebellionRider.com’, 0, 14 ) FROM dual;
In the above query we set the starting position for our sub string on Zero and the Length of substring on 14. This means the search for the substring begins at position 1 that is from the first ’w’ of ‘www’ and the 14 characters from that position onward are extracted, yielding the substring ‘www.RebellionR’.
Scenario 5: When starting position (start_pos) is set To a Negative number
In this scenario when user sets starting position of the second argument of SQL Substr function to a negative number say -3 then oracle engine counts backward from the end of the source string
For example
SELECT substr( ‘www.RebellionRider.com’, -9, 5 ) FROM dual;
Here searching of sub string will start from the character R of “Rider” as it is at the 9th position from the end of the source string. Then five characters from that position onwards are extracted, yielding the substring ‘RIDER’.
Scenario 6: When the length of substring (substr_length) is omitted
I have already mentioned above that the Third argument of SQL Substr function is optional, which means that it’s not mandatory for you to specify the length of substring. In case the user does not specify the length for Sub string then Oracle engine returns all characters till the end of source string.
For example
SELECT substr( ‘www.RebellionRider.com’, 5 ) FROM dual;
As you can see in the above query I didn’t specify the third argument of SQL Substr function which is Substr_length (sub string length). In this case oracle engine will start searching substring from the specified position which is 5 in our query and all the characters from that position onward till the end are extracted, yielding the substring “RebellionRider.com”.
That’s all about SUBSTR Function. Hope you enjoyed reading. Do make sure to Like & Share this article on your social network & help me in reaching out to more people. Also, connect with me on my Facebook page.
Thanks & have a great day!
