 Hello friends, welcome to SST Unitech, my name is Susheel Singh and this is Continuation of SQL Server Interview Questions and Answers. So today I am going to discuss a very common question generally interview, ask this question to us. What is the real time huge off-function or stop procedure? If we have stop procedure in our SQL Server then what is the requirement for the function? We can done all these things which can be possible in function by using stop procedure. Then what is the scenario by which we can use function and we cannot use stop procedure. So let's move to another slide where I have created a scenario by which we can get the output by using function but we cannot get the output by using stop procedure. So this is our slide as you can see we have input table and we have two output tables. So in input table we have two columns first is ID and second is name. In ID we have integer data and in name we have alphanumeric data. In our output you can see we have two tables first is for the integer value and second is for the string values. So in our first we want to get the output where we want only integer values are present in name. In our second output we want only string values are present in our input table. So in our second table we want to get only string values which are present in name column in our input. So let's move to SQL Server Management Studio where we will discuss how we can get the output. So I am using SQL 2014 you may have another version of SQL Server but query would be same for all the versions. So I have already typed the query so it would save the time we can create the table. So table has been created successfully and then I am going to insert some record on it. So five rows affected so five rows has been inserted. So this data which I have in this table and as I have displayed in our slide both are same. There are five rows in our slide and we have in our input table we have five rows in our input table. So before going forward I would like to explain about the PAT index of string and replace functions. So let's start with PAT index. PAT index is very similar to CAR index. The only difference between PAT index and CAR index we cannot use wild card characters in CAR index but we can use in PAT index. So let's create a scenario by which we can easily understand about the PAT index. So select then we have to specify the PAT index and then the search pattern. So what value we want to search like we want to search PAK and then from where we want to search we have to specify our second parameter. So we want to search from this string. So from this string we want to search PAK. Now we can execute and we can see it's going to return four and we can count in our second parameter string D then double E then P. So P comes in fourth position. So it's going to return four. So this is our PAT index and it's very easy to understand. So now let's move to servicing. So servicing function is used to fetch the specific length string from any string. Let's create a scenario by which we can understand about the servicing function. So here servicing then expression. So here we have to specify the expression by which we want to fetch the string. So we have to specify the string. So it's D PAK. And now we can see starting position. So starting position we want to and then the length. How many characters we want to get from this string? We want only two characters and then we can execute. Now we can see it start from E and it will pick these two characters in our output. So we can see and it's very easy to understand about the servicing function. Move to replace function. So in replace function select and then replace select then replace and here we have to specify the string by which we want to replace. We want to replace from D PAK. What character we want to replace? I want to replace E and then from which character we want to replace by T. So close the bracket and execute. So now we can see in our output E has been replaced by T. So these are the three functions I have explained here. So now I am going to use all these functions to get the output. So now I have already typed the query. So let's move to our query. So this is our query. So I have declared a variable which is string type and I want to use a loop and inside this loop I want to check if the value present in this string variable is not an integer value because it's going to check between 0 to 9. If the value present in this string variable is not between 0 to 9 then it will return the location of that character value. So inside this loop we can see So our inner function is PAT index. As you can see this is our inner function. So I am going to explain about this PAT index. So PAT index is going to return the first non-integer value. So it will return the first non-integer value's location. Then I have used this sub-string function. So in sub-string function it will return the first non-integer character because PAT index will return the location where first non-integer value character is present. If it's in first position then it will return 1 and then in sub-string function I am going to use this PAT index so it will start from 1 and we want to fetch only 1 from this and then our last function is replace function. So replace function is used to replace this non-integer value by blank. Once it would be replaced then it will again check and then again check until unless value is not only integer. So let's create a function by which we use this formula. So I have already created the function. So this is our function. I want to execute. So function has been created successfully. So we can use this function as inline function. So this is our table as I have explained. So select store from this table and then we can see and now we can see value is present. We can use this function and it takes a parameter. So in parameter I want to use name and now I want to execute. So we have to use comma then dbo dot function name and now I am going to execute. So here we can see our string value has been replaced by blank and now I want to use so now we can get only integer values from this input string. So we cannot use the store procedure to get the same output in inline query but in function we can get the inline query and now as we can see we have used pat index where this is not equals to so we can use equals to here I want to use one more function and now name of this function is get integer value get string value and now I want to replace this not equal to sign. I want to execute and we can get the output. So function has been created successfully and we can use this function over here in our select query then put comma dbo dot then the function name and now we can use name and now we can execute and we can see it will return the output. So there is some mistrack I did we have to alter this function because we have to replace this not equal to sign over here as well. So now alter this function and now execute this query and now we can see we get the output. So this is for integer values and this is for string values as I have displayed over here so one output is for integer another output for string. So we cannot use store procedure in inline query but we can use functions with inline queries so this is the difference between store procedure and function where we cannot use store procedure and we can only use functions. I hope you have understand the real time use of function over store procedure. If you like this video please subscribe our channel for many more videos if you need this script it will be available on my Facebook page you can copy from there. Thank you so much.