SQLcommitted

1. Difference between replace and Stuff function.

Answer: Replace function  replaces all occurrences of a specified string value with another string value.

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Replace function returns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REPLACE returns varchar and it returns NULL if any one of the arguments is NULL.

Stuff function returns character data if character_expression is one of the supported character data types and  it returns binary data if character_expression is one of the supported binary data types.

Ex: Select Replace (‘ABC123DEF123’,’123’,’—‘)

The above code will replace all occurrence of 123 with — and output will be ABC—DEF—

Select Stuff(‘ABCDE’,2,3,’000’) will return A000E

February 8, 2012 Posted by SQLcommitted | SQL Server Interview Questions, String Functions | 1 Comment