 Hey guys, welcome to SSUnitech Social Decide and this is continuation of SQL Server real-time scenarios. So, today we are going to see about the concat.ws function. So, this function was introduced in 2k17 and today we are going to see what is the real-time use of this function and how we can use that. So, basically what concat.ws function does. So, concat.ws function stands for the concatenate with the separator. And it is a special type of concat function which is already available inside the SQL Server. So, the first argument is a separator and that separator could be used to separate the rest of the arguments. So, the separator is added between the strings to be concatenated. The separator can be a string as can be the rest argument also be a string. So, what it means? So, as you can see in the below table on the left side. So, it has three columns. First is the first name, second would be the middle name and third will be the last name. And here we are going to concatenate between all these three columns along with the separator that is dash. So, in the full name you could see the cane, then dash, then j, then dash and accordingly we are going to separate. If we are having any null values, any one of these columns, then we are going to ignore those null values. For example, in the second last row you could see the ss, then null, then unitet in the last name. So, in the full name we could see only ss, then dash, then unitet. So, here we don't have double dash. So, such type of scenarios can be handled directly by using concat underscore double s function. So, before going to use this function, if we don't have this function, then how we can achieve this output that we could see over here. So, for that go to other SQL server and we will see without using concat function and with using concat function. So, as you can see we are having AdventureWorks took a 17 in our system and here we are having the person.person table. So, in the person.person table we could see we are having first name, middle name and last name. Now, the first thing that we want to concatenate between all these three. So, the first thing that we could use directly the first name plus middle name plus last name. So, this will be going to concatenate between all these three functions. So, first we need to correct this like that. If we execute then we could see it is going to return the output but not as expected. So, we could add dash between these. Now, again here we could see we are having the dash but if we are having any null value, so it is going to ignore those null values. So, we need to check the null values. So, first go for the first name then middle name then last name. So, here we need to check is null if the first name along with the dash is having null. So, it means if the first name is null then everything will be null and we want to return as without any character. Then we need to check for the middle name. So, here we could check is null along with this dash. So, put common and we don't want to do anything from here. Then at the last again we need to check is null. Then we need to check if that value is null then we are going to return as nothing. Now, let me try to execute this query. So, it is going to return the output as we are expecting. As we could see over here we are having everything as per our expectation. But here we need to do a lot of things that you could see. So, this is the first way by using this we could achieve this but we are also having the second way. So, let me copy this and paste in the new line and after that we could use the concatenation that is available directly over here. So, this plus will be gone and we need to close the bracket over here. Now, let me try to execute this. So, this will again going to return the same value as per our expectation. But as you can see we need to check the null values then we need to replace those null values. So, here we are doing a lot of calculations. So, we can directly use a concat underscore ws function to achieve this output. So, we could use the concat underscore ws. So, this is the function start the bracket. So, the first argument will be the separator. So, here what is the separator that is dash then put comma then we need to specify the column names. Those we want to concatenate. So, our first column name is the first name, second column name is the middle name, third column name will be last name and we can close the bracket and let me try to execute this query. So, here we could see we are going to return the same output as per our expectation. But here we don't have any type of calculation inside the argument. So, as per the performance this will be very good instead of using the concat or is null for checking all these values or the parameters. If we need to add any another parameter over here then we could directly add those. So, it will not have any problem. We could directly as for example the title. So, title can be added over here and we can execute. So, it is going to return the value for the title as well. Here you could see because here we have the title like miss or mister. So, here we could see the titles. So, this is very easy to add any new columns or removing any columns and we don't need to check for the is null or null values at adding the separator. So, previously we had to do a lot of calculations but after introducing this function we can directly achieve this output. So, I hope guys you have understand how we can use the concat underscore WS function in the real time. And if you like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. See you in the next video.