Let's learn another string function, STUFF(). It looks like this:
STUFF(string, start, length, newString);
STUFF() inserts a new string (the fourth parameter) into another string (the first parameter). The second parameter specifies where the string should be inserted, and the third parameter specifies how many characters of the old string to delete. Here's an example:
SELECT STUFF(N'the simple example', 5, 6, N'easy');
From N'the simple example', we cut the word N'simple'. The s of 'simple' is at position 5, and the word is 6 letters long. In its place, we put the word N'easy'. Now we have N'the easy example'.
The marketing agency manager wants us to prepare a report. It has to contain a list of slogans: the slogan text, and the type of that slogan. The manager only wants to see slogans of the N'tv commercial' type.
Show the text of each TV slogan and replace the word N'commercial' with the world N'advertisement' in the N'tv commercial' type. Name the last column Type.
Use STUFF(Type, 4, 10, N'advertisement') to replace the word commercial with the word advertisement.
