STUFF and CONCAT in SQL

STUFF and CONCAT in SQL

Hello friends, We have already explained you regarding RANK(), DENSE_RANK(), ROW_NUMBER() , Aggregate functions in sql server and Identity column in sql server. Today I am going to write how to Concatenate string in SQL. There are different technique to concatenate string. But I will explain STUFF and CONCAT functions.

STUFF :-

STUFF() function used to delete sequence of characters from specified position of first string. It also insert sequence of characters at specified position in first string.

Syntax :

STUFF(SourceString, Start, Length, InsertString)

SourceString
Is an expression of char data. It can be sequence of char data through variable or constants.
Start
Is integer value that specifies the starting position for insertion and deletion of string. If it’s value is negative, then it returns null string. If value is longer than count of SourceString then also it returns null string.
Length
Is integer value that specifies the number of character to delete. If it’s value is longer than SourceString , then it will delete all character till end.
InsertString
Is an expression of char data. It can be sequence of char data through variable or constants. This will replaced Length of character from Start position in SourceString character data.

Example:-

SELECT STUFF('I LOVE INDIA',7,0,' MY');

STUFF()

CONCAT :-

CONCAT() function is used to concatenate string supplied in this function. This function handle NULL value. If supplied string will be NULL or blank space then, it will ignore it and return string. This function takes multiple argument with comma separated. This function is just introduced in SQL Server 2012. So, you need SQL Server 2012 to use this function.

Syntax :

CONCAT(string1, string2 [, stringN ] )

It will concatenate string1,string2,etc. in one string. So, it will return string value.

Example:-

SELECT CONCAT('A','B','C',NULL,'D')

CONCAT()

Conclusion
I have explained STUFF and CONCAT functions. STUFF allow you to insert/delete string from in between string whereas CONCAT allows you to append string.

Thanks for reading 🙂

Leave a Reply