 Hey guys welcome to SSUnit tag so see this side and this is continuation of sequence over real type scenarios. So today we are going to see about the custom string split function. So if you are going to use the video version of the 2k17 then this function was not available over there. So you can write this script that we are going to write in this video and this script will be going same as the string split function because this function is very important while you are going to work inside the real time environment. So go to on the SSMS and we will try to write the function. So here this is the string split function. So it will accept two parameters the first parameter that will be have the values with any separator. So here the separator is comma. So the second parameter that should be the separator that is comma. So if we have executed this then inside the output you could see only one column that is value and all these values those are separated by comma in the rows that you could see. So this is very powerful function. So for example if you are going to write a store procedure and where we have a input parameter with the employee ID and that employee ID is having comma separated values and you want to filter out the data based out that input parameter. So first you need to convert all these comma separated input parameter value that is employee ID to a single column and after that you can do the filter. So this function will be going help us directly. So I will show you in the next video how you can use that in the store procedure. In this video I am going to show you how you can write this string split function. So first you have to write the create statement. So create then the function and after that you need to specify the function name. So I am going to call this as fn string split and it will again ask to input parameter first parameter that should be your input value and we can call this as worker max. The second parameter that should be the separator. So it should be the separator and the data type that should be worker 10. So this is for the input parameter of the function. Next you need to output that will have the table. So returns add the rate table the data type that should be table again and it will have only one column and that column is value column and what will be the value. So that value should be worker and it should be 100. So like that we have next we can use as then we can have the begin and end. So in between we need to write the logic of the function. So this is the syntax of the function. So as you can see the create then function the function name after that it is having to input parameter first is the actual value second is the separator and in the output we want to have a table and it is having only one column that is value and the data type that should be worker. Now first we need to declare a variable. So we can declare a variable that is a temporary variable and the data type of this variable that should be worker 4000. Now our main logic is going to start from here we need to use a loop. So while first we need to check the length of this input variable if this value is greater than 0 then this loop will be going to execute and in between we need to write the logic. So what will be the logic? So the logic is very straightforward we need to set the value on this temporary table. So first as you can see in the input so it is having this value. So before this comma we need to fetch the value on the temporary variable. So here for comma we need to check characteristic and after that we want to find out the comma. So here that is a variable that is separator then put comma then we want to fetch this from this input variable. So it is going to fetch where the comma is available. Now we can use the left function. So left after that we want to fetch this from the input variable then put comma. Now we can close the bracket. So here it is going to fetch the value but it will also include the comma. So we can have minus 1. So now it is going to fetch out where the first comma is available and minus 1. So in this case as you can see 1, 2, 3, 4, 5 and 6. So it should be going to return 5 from this care index and this left function is going to use from this input string. But here we need to notice one thing for example we do not have any comma. So this is not available right here only this value is having. So on that particular case this care index will not find any value. So it should be 0 and this parameter is going to replace with minus 1. So this value will be going to have minus 1. So it will be going to return an error. So how we can handle that? So for that first we need to check by using nalif if this value is going to return minus 1. So on that case hold this we can remove this. Now you can see in this case it is going to check if this value is going to return minus 1 then it is going to check with the minus 1. If it is minus 1 then it is going to return as 0. Second we can use isnal function and this isnal function is going to replace this minus 1 with the actual length. What is the actual length of your input variable? So that is it. So this time you can see it is going to return the value if this is minus 1 then it is going to return the complete length whatever the length of the input that is available. Now second we need to set your input variable value is equals to. Now we have set the first characters that you can see before the comma. So next time we can remove this from this input string and in the input string we will be going to have only this value. So how we can do that? So for that again we need to find out the care index for the separator. So care index here we want to find out from the separator then put comma. And it should be your input string. So this is going to return the first comma from the left side. So we can use the substring function directly and this time we want to get the value from this input string. This is going to start from here then we can put the comma. How many characters we want? That should be the third parameter that we can directly mention the length of your input variable. Now we can close the bracket here one bracket and two bracket are missing now it looks good. So here first we can see it is going to start where the first comma value is available and after that it is going to move forward. But here we need to notice one thing this care index is going to return as 6. It is also include the comma but we don't want the comma so we can add one right here. So now it is going to start by next character after the comma but here we need to notice one thing for example if this value is going to return as 0 then on that scenario what will be the case? So it is 0 then it is going to start from 1 and whatever the length that is going to pick directly. So if we don't have anything left on your input string so on that scenario it is going to return as 0 and here you could see it is having 1. So it is again going to start from your starting position so that case we don't want. So here we want to check nullif if this value is 0. So we can use the is null function so here we can use the is null and after that here we can get the whatever the length of your input string then plus 1. Now it looks good everything is ok only one thing is remaining that we need to do. So we want to insert the value on this table. So let me copy this and here we can write the insert statement like insert into your table and in the values we want to insert from this temporary variable. Everything looks good here we need to return now this one is ready let me try to execute this. So it is executed successfully let me copy this function and go to a new query window and let me try to select right here. So select a stick from this function inside the bracket the first parameter and second parameter. So first parameter could be anything and in the separator that should be comma let me try to execute this. So once we execute then we can see everything is going to convert successfully from this comma to a table so everything is ok. So if you have still any doubt on this function then you can drop your questions in the comment box I will try to respond over there. Thank you so much for watching this video in the next video we will see how we can utilize this function in the real time inside the store procedure. See you in the next video.