REPLACE function

REPLACE function

REPLACE (string_expression1 , string_expression2 , string_expression3)

replaces in the string_expression1 all the occurrences of the string_expression2 with the string_expression3. This function is absolutely helpful in UPDATE operators, if we should change the content of the column. For example, let we are needed to replace all the spaces with the hyphen in the names of the ships. Then we can write

UPDATE Ships
SET name = REPLACE(name, ' ', '-');

However this function can also find its appliance in more non-trivial cases. Let’s determine how many times the letter “a” is used in a name of a ship. The idea is quite easy: let’s replace every searching letter with any two characters, and after that we’ll calculate the difference of lengths of the string we got and the source string. So,

SELECT name, LEN(REPLACE(name, 'a', 'aa')) - LEN(name)
FROM Ships;

And what if we need to determine the number of occurrences of a random sequence of characters that, say, we get as a parameter of a stored procedure? In this case we should complement the used above algorithm with the division on a number of a characters in a sequence:

DECLARE @str AS VARCHAR(100)
SET @str='ma'
SELECT name, (LEN(REPLACE(name, @str, @str + @str)) - LEN(name))/LEN(@str) FROM Ships

You should be careful when using this method to calculating the number of spaces. Apart from the fact that LEN function does not take into account trailing spaces, the result also depends on data type.

In the string “World Wide Web” there is 14 characters. Let’s replace each space with 2-character string and calculate the length of the string before the replacement and after that:

declare @w char(50) ='World Wide Web'
select len(replace(@w,' ','xx')) after, len(@w) before

We wanted to add 2 characters but are obtaining 88 instead of 16.  This is due to the CHAR being strict data type, so the length of any string must have the specified value. We had specified 50 characters hence the value of variable will be padded by trailing spaces up to 50 characters as a whole. So we’ll get (50-14)х2 = 72, and then 16 + 72 = 88.

But if we’ll declare the variable as VARCHAR(50), we obtain the desired result:

Suggested exercises: 86