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

Key Takeaways

hey YouTube welcome back once again this is Manish from dealer.com today in this Oracle database video we will see second character manipulation function which is SQL substa function as the name suggest SQL subst function will return sub string from the given Source string let's see the syntax as we can see SQL subst function takes three parameter first one is Source string from which you want to extract the segment second parameter is a starting position of the sub string from The Source string and the third parameter is substr length which is the length of the sub string first two parameters are mandatory to specify while the third one is optional so we can see SQL substr function will return return a sub string of a specified length from a source string beginning at a given position first parameter Source string can be of any data type care where care to n care andw care to clob or n clo whereas both start position and subst length parameters must be number data type the returning result of SQL subsq function is of same data type of source string let's see some example okay here we are on SQL Developer let's do some example here in this query URL of my website ww. Rebellion rider.com is our source string with the total length of 22 characters now I want to extract the name of my website that is rebellion Rider 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 lens also the name of the website Rebellion Rider is starting from the fifth position therefore I have specified five at second parameter of SQL substr function which is start pose or starting position execute it here is our result Rebellion Rider there are few scenarios related to SQL substr function let's check them first scenario when a starting position is larger than the length of source string in this case SQL subst strr function will return null as a result Let's do an example 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 subst Str length is greater than the source string in this case is the segment return is the sub string from starting position to the end of the string for example our starting position is at 5 means at the first hour of rebellion Rider and the length of sub string is set to 23 which is greater than the length of source string that is 22 execute as you can see we get a sub string from first R of rebellion Rider till the end of source string third scenario when you supply numeric or arithmetic expression or a date instead of character s Source string to SQL substr function in this scenario if you have supplied a numeric string instead of a character as source string the Oracle engine cast them as a corrector when they occur as a parameter to SQL substa function and if you have supplied arithmetic expression or a date then the Oracle engine first solve or evaluates the arithmetic expression or the date then cast them as a corrector 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 corrector let's see some examples Oracle first evaluates the arithmetic operation that is 50,000 - 7 = to 49,93321 character string means 49,990 will be a five cor string starting position of sub string is two that means from the first n of 49,990 we specified the length of Serv string is four so we must get 9,993 as a result let's check execute here is result similarly if you give a CIS deed as your Source string it first get evaluated and then gets casted Let's do an example I have a specified CIS date here without any quote so that you can see that CIS date first get converted into date then get casted into corrector string by SQL Serv function let's execute here is our result there are other scenarios also such as what if starting position is zero or if it's a negative number or third parameter subst strr length is omitted for these scenarios please read my blog I have explained everything there you can find the link of this blog in the video description below that's it for today hope you enjoyed watching this video don't forget to subscribe and like I'll see you in the next video soon thanks for watching this is Manish from Rebellion Rider