HomeОбразованиеRelated VideosMore From: Manish Sharma

Oracle Database11g tutorials 13 || SQL substr function / SQL substring function

304 ratings | 82690 views
link of SQL substr blog : http://www.rebellionrider.com/SQL-substr-function.htm 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 https://copy.com?r=j7eYO7 Contacts E-Mail [email protected] Twitter https://twitter.com/rebellionrider Instagram http://instagram.com/rebellionrider Facebook. https://www.facebook.com/imthebhardwaj Linkedin. http://in.linkedin.com/in/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
Html code for embedding videos on your blog
Text Comments (18)
YAYAH sarkodie (4 months ago)
thank you.
Cedeta Fra (9 months ago)
Thanks for the subtitles. They are really helpfull
sheik abdullah (1 year ago)
select 'A_B_C' s1,substr('A_B_C',1,instr('A_B_C','_',1,1)-1) s2,substr('A_B_C',instr('A_B_C','_',1,1)+1,(instr('A_B_C','_',1,2)-1)-instr('A_B_C','_',1,1)) s3,substr('A_B_C',instr('A_B_C','_',1,2)+1) from dual;----output please
sheik abdullah (1 year ago)
select '[email protected],[email protected],[email protected]' Strig,substr('[email protected],[email protected],[email protected]',1,instr('[email protected],[email protected],[email protected]',',',1,1)-1)---output please select '[email protected],[email protected],[email protected]' Strig,substr('[email protected],[email protected],[email protected]',length(substr('[email protected],[email protected],[email protected]',1,instr('[email protected],[email protected],[email protected]',',',1,1)-1))+2,instr('[email protected],[email protected],[email protected]',',',length(substr('[email protected],[email protected],[email protected]',1,instr('[email protected],[email protected],[email protected]',',',1,1)-1))+1,1)-1) select '[email protected],[email protected],[email protected]' Strig,substr('[email protected],[email protected],[email protected]',(length(substr('[email protected],[email protected],[email protected]',1,instr('[email protected],[email protected],[email protected]',',',1,1)-1))+length(substr('[email protected],[email protected],[email protected]',length(substr('[email protected],[email protected],[email protected]',1,instr('[email protected],[email protected],[email protected]',',',1,1)-1))+2,instr('[email protected],[email protected],[email protected]',',',length(substr('[email protected],[email protected],[email protected]',1,instr('[email protected],[email protected],[email protected]',',',1,1)-1))+1,1)-1)))+3) s1 from dual;--output please
Happy Manu (1 year ago)
Thnxxxx sir
mack paji (1 year ago)
Thanks Sir your videos are greatly helpful for me but sir you forget to tell about other character manipulation functions after substring as you move forward to creating table also I'd did't find about that functions in www.rebellionrider.com also.Please help me sir.And thanks again.
Mohammed Junaid (2 years ago)
Thanks Sir
Salah Ali Abdi (2 years ago)
Easy to understand sir Manish but little bit speedy
Abdullah saad (3 years ago)
great video and very good job
Khyati Patel (3 years ago)
very nice video.............
Jayam Koko (3 years ago)
can i know if i want to display under where clause in an address field to check whether 'ad' is in that field how i can do that?
Jayam Koko (3 years ago)
Morshedul Islam (3 years ago)
Extra ordinar. thanks a lot
ABDUL WAHAB Najeeb (3 years ago)
Really good one. Thumbs up
you are really good.....awesome.....
Nabi Ayran (4 years ago)
its very good tutorial
Dheemanth Bhandarkar (4 years ago)
Very well explained. Thank you for clearing my doubt
Tuhin Subhra Samanta (4 years ago)
Thanks man  :) it was helpful

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.