link of SQL substr blog :
www.rebellionri...
SQL substr function
SQL Substr function will return a sub string of a specified length from the source string beginning at a given position.
Tool used in this tutorial is command prompt.
This tutorial series is part of SQL expert exam certification training. if you are preparing for SQL certification you can use my tutorials. This SQL Tutorial is a part of free training.
Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage
copy.com?r=j7eYO7
Contacts
E-Mail
RebellionRiderYT@gmail.com
Twitter
/ rebellionrider
Instagram
/ rebellionrider
Facebook.
/ imthebhardwaj
Linkedin.
/ mannbhardwaj
Thanks for linking, commenting, sharing and watching more of our videos
This is Manish from RebellionRider.com
----------------------------------------------------------------------------
As the name suggests SQL Substr function will return substring from a given source string.
Let's see the Syntax
Substr (source_string, start_pos, Substr_length)
As we can see SQL substr function takes 3 parameters. First one is Source string from which you want to extract the segment.
Second parameter is Starting position for sub string from the Source string.
And the third parameter is Substr_length which is the length for the substring.
First two parameters are mandatory to specify while third one is optional.
So we can say.
SQL Substr function will return a sub string of a specified length from the source string beginning at a given position.
First parameter source string can be of any data type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB whereas both start_pos, Substr_length parameters must be number data type.
The returning result of SQL Substr function is of same data type of source string.
Let's see an example of SQL Substr function.
SELECT substr('www.RebellionRider.com',5,14) FROM dual;
Here in this query url of my website www.RebellionRider.com is our source string with the total length of 22 characters,
Now, I want to extract the name of my website that 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 which is substr_length.
Also the name of the website RebellionRider is starting from 5th position therefore I have specified 5 at second parameter of SQL substr function which is strt_pos or starting position.
Execute it.
Here is our result RebellionRider
When starting position is larger than the length of 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 at starting position and the total length of our source string is 22 characters
Let's execute
And the result is Null.
Second scenario
When the Substr_length is greater than source string
In this case the segment return is 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 means at 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.
Execute.
As you can see we get a substring from first R of RebellionRider till the end of the source String.
Third scenario
When you supply numeric or arithmetic expression or a DATE instead of character 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 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 some example.
SELECT substr(50000-7,2,4) FROM dual;
Oracle first evaluates the arithmetic expression that is
50000-7 equals to 49993. And then oracle engine casts this result 49993 into a character string. Means 49993 will be a 5 characters string.
Starting position of substring is 2, that means from the first 9 of 49993
We specified the length of substring is 4 so we must get 9993 as our result.
Let's check execute
21 авг 2024
