STUFF() and CHARINDEX()in SQL Server.
1)Stuff():-
The Stuff() can be used to stuff a string into another string.
It inserts the string at a given position and deletes the number of characters specified from the original string.
Syntax:-
STUFF(string, start, length, new_string)
where,
string: Original string to be modified.
start: The given a length of characters will be deleted and a new sequence of characters will be inserted.
length: The numbers of characters to be deleted from the starting index in the original string.
new_string: The new string to be inserted in the place of deleted characters from the starting index.
Example:
- Stuff function inserts a string into another string at a specific location.
SELECT STUFF(‘SQL Tutorial’, 1, 3, ‘SQL Server’) result;

2) Stuff function to convert time from HHMM to HH: MM
SELECT STUFF(‘1015’, 3, 0, ‘:’) AS time_format;

2) Charindex():-
CHARINDEX functions return the location of a substring in a string.
The search is NOT case-sensitive.
Syntax:-
CHARINDEX(substring, string [, start_location])
where,
substring: The substring that you want to find.
Its length is limited to 8,000 characters.
string: string can be a literal string, expression or column.
start_location: The location in the string where the search will start. The first location is 1.
Example:
1)Charindex function performs to a single string.
SELECT CHARINDEX(‘SQL’, ‘SQL Server CHARINDEX’) position;

2)Charindex function to perform a case-insensitive search.
SELECT CHARINDEX(‘SERVER’, ‘SQL Server CHARINDEX’ ) position;

If you are new to SQL Server start with the following must-watch video: -
