REPLACE function | Interactive tutorial on SQL | SQL-tutorial.ru

Русский

English

02:09

			Content	Login

		SQL Problems and solutions S. I. Moiseenko

back go forward

	Transact-SQL string and datetime functions String functions in MS SQL SERVER 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, ' ', '-') Notes: This example you can perform on the page with the 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, 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 string functions REPLACE LEN DML exercises

Previos [REVERSE function]

[REPLICATE and STUFF functions] Next

Last added:

donate

Tags

aggregate functions ALL ASCII AVG Battles Bezhaev Bismarck C.J.Date Cartesian product CASE cast CHAR CHARINDEX classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CTE data type conversion database schema DATEDIFF DATEPART DATETIME DELETE DISTINCT EXCEPT exercise (-2) exercise 19 exercise 23 exercise 37 exercise 39 exercise 46 exercise 54 exercise 55 exercise 56 exercise 57 exercise 7 exercise 70 exercise 8 exercises EXISTS FROM FULL JOIN GROUP BY grouping Guadalcanal More tags

The book was updated
yesterday

©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.