Today I am going to discuss two string functions STUFF and REPLACE.
We all know very well that whenever we add null in a string, it result null. It doesn't matter the position of NULL in string.
Look at below three statements. I added NULL in different select statement on different place. All three statements give result as NULL.
That why I call NULL as "DEFUSER". It’s only my own opinion.
Now, i am going to replace some words in a string with the help of REPLACE function.
REPLACE ('String_1', 'String_2' ,'String_3')
REPALCE function finds String_2 in String_1 and replaces it with String_3.
Check below link for further information.
Below, I have written down few select statements with REPLACE function below.
Statement 1. REPLACE function work perfectly and result as "ABCTT_%_TTEFG".
Statement 2. This statement give result as NULL", because REPLACE function gives NULL and when we add NULL with other string, it will result as NULL.
Statement 3. In this statement, we try to find "Y" in string “TT_D_TT”. But "Y" not found, so it's not replace any string and result as "ABCTT_D_TTEFG"
Statement 4. This statement returns "NULL" because in REPLACE function we use "NULL". It's surprising behavior of REPLACE function.
Now again, I am going to replace some words in a string. But this time I am using STUFF function.
STUFF ('String_1', [Start_Position], [Length], 'String_2')
STUFF function, first delete No of Character from Start point and then replace "String_2" in "String_1". Check below link for further information.
Below, I have written down few select statements with STUFF function.
Statement 1. In MS BOL (Microsoft Book online), If [Start_Position] or [Length] are NULL then STUFF function return NULL. So it results as NULL.
Statement 2. In MS BOL (Microsoft Book online), If [Start_Position] or [Length] are NULL then STUFF function return NULL. So it results as NULL.
Statement 3. STUFF function work perfectly and result as "ABCTT_%_TTEFG".
Statement 4. Here, STUFF function return String expect then NULL value. It's difference between REPLACE and STUFF function.
Now I am using these two functions in CONCAT function. CONCAT function is used to concatenate multiple strings in sql server.
Check out below statements.
Statement 1. In this, REPLACE function returns NULL. In CONCAT function, if value is NULL then it consider as blank value. So this statement returns "ABCEFG".
Statement 2. STUFF function return "TT__TT". So this statement returns "ABCTT__TTEFG"
Conclusion: - We need to take care of this behavior of STUFF function for future use. It’s very important when we are spending our most of time playing with string.
I hope this will help you. Your comments are most welcomed.
Keep Learning, Keep Growing!!!