Русский    English

REPLACE function

REPLACE function

  1. 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

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


This example you can perform on the page with the  DML - Data Manipulation Language - sublanguge of SQL including SELECT, INSERT, UPDATE and DELETE statements.DML exercises, where the questions on updating the data are admitted

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,

  1. SELECT name, LEN(REPLACE(name, 'a', 'aa')) - LEN(name)
  2. 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:

  1. DECLARE @str AS VARCHAR(100)
  2. SET @str='ma'
  3. 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:

  1. declare @w char(50) ='World Wide Web'
  2. SELECT len(REPLACE(@w,' ','xx')) after, len(@w) before

after    before
88    14

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:

after    before
16    14

Suggested exercises: 86

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100