Transact-SQL string and datetime functions

String functions in MS SQL SERVER

REPLACE function

REPLACE (string_expression1 , string_expression2 , string_expression3)

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,

Console

Execute

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

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

    Scheme

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: