SQL Server функция REPLACE
В SQL Server (Transact-SQL) функция REPLACE заменяет последовательность символов в строке другим набором символов, и не чувствительна к регистру.
Синтаксис
Синтаксис функции REPLACE в SQL Server (Transact-SQL):
Параметры или аргументы
string1 — исходная строка, из которой последовательность символов будет заменена другим набором символов.
string_to_replace — строка, которая ищется в string1 .
replacement_string – строка для замены. Все вхождения string_to_replace будут заменены на replace_string в string1 .
Примечание
- Функция REPLACE выполняет замену, которая не учитывает регистр. Таким образом, все вхождения string_to_replace будут заменены на replace_string независимо от регистра string_to_replace или replacement_string .
- См. Также функцию STUFF.
Применение
Функция REPLACE может использоваться в следующих версиях SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005
Пример
Рассмотрим некоторые примеры SQL Server функции REPLACE, чтобы понять, как использовать функцию REPLACE в SQL Server (Transact-SQL). Например:
Name already in use
sql-docs / docs / t-sql / functions / replace-transact-sql.md
- Go to file T
- Go to line L
- Copy path
- Copy permalink
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
Replaces all occurrences of a specified string value with another string value.
. image type=»icon» source=»../../includes/media/topic-link-icon.svg» border=»false». Transact-SQL syntax conventions
string_expression
Is the string expression to be searched. string_expression can be of a character or binary data type.
string_pattern
Is the substring to be found. string_pattern can be of a character or binary data type. string_pattern must not exceed the maximum number of bytes that fits on a page. If string_pattern is an empty string (»), string_expression is returned unchanged.
string_replacement
Is the replacement string. string_replacement can be of a character or binary data type.
Returns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REPLACE returns varchar.
Returns NULL if any one of the arguments is NULL.
If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type.
REPLACE performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.
0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in REPLACE.
The following example replaces the string cde in abcdefghicde with xxx .
The following example uses the COLLATE function.
The following example calculates the number of spaces in a sentence using the REPLACE function. First, it calculates the length of the sentence with the LEN function. It then replaces the ‘ ‘ characters with » with REPLACE . After this process, it calculates the length of the sentence again. The resulting difference is the number of space characters in the sentence.
SQL Server REPLACE Function
Summary: in this tutorial, you will learn how to use the SQL Server REPLACE() function to replace all occurrences of a substring by a new substring within a string.
SQL Server REPLACE function overview
To replace all occurrences of a substring within a string with a new substring, you use the REPLACE() function as follows:
- input_string is any string expression to be searched.
- substring is the substring to be replaced.
- new_substring is the replacement string.
The REPLACE() function returns a new string in which all occurrences of the substring
are replaced by the new_substring . It returns NULL if any argument is NULL.
SQL Server REPLACE() function examples
Let’s take some examples of using the REPLACE() function to understand how it works.
A) Using REPLACE() function with literal strings
The following example uses the REPLACE() function to replace the tea with the coffee in the string ‘It is a good tea at the famous tea store.’ :
Here is the output:
As you can see from the output, all occurrences of tea were replaced with coffee .
B) Using REPLACE() function with table columns
In this example, we will use the sales.customers table from the sample database:
This example calls the REPLACE() function twice to format the phone number in a new format:
The following picture shows the partial output:
- The first call REPLACE(phone, ‘(‘, ») replaces the character ‘(‘ in the phone number by a space e.g., (916) 381-6003 -> 916) 381-6003
- The second call reuses the result of the first call and replaces the character ‘)’ by a space e.g., 916) 381-6003 -> 916 381-6003 .
C) Using REPLACE() function to correct data in tables
The REPLACE() function is often used to correct data in a table. For example, replacing the outdated link with the new one.
The following is the syntax:
For example, to change the city code of the phone numbers from 916 to 917, you use the following statement:
Notice that you should back up the table before performing replacements.
In this tutorial, you have learned how to use the SQL Server REPLACE() function to replace all occurrences of a substring, within a string, with a new substring.
SQL Server Replace
By 
Roja Metla
Introduction to SQL Server Replace
Replace function is used to replace all the occurrence of the substring that you have specified. Normally we specify three arguments in the Replace function.
1) Actual_string
Hadoop, Data Science, Statistics & others
2) Old_substring
3) New_substring.
Here Actual_string resembles the string which you want to make the changes take place. Old_substring is the substring of Actual_string which you want to be changed to the New_substring.
We can perform replace operation in the database column as well. We need to specify the required column that need to replace with the New_substring.
In this session let us learn a few things like How and Where the “Replace function” is used.
Syntax:
Now let us see the syntax for the replace function:
Now let us see the syntax for the replace function if we want to perform the operation on the column of database table:
How to use Replace in SQL Server?
Now let us see how the replace function works in the SQL server:
Output:
Now let us replace the column with some particular string. The thing to remember if u specify a replace function in a column whole column will be replaced with the mentioned string.
Let us create a table with default data in it and perform the replace function in one column.
Table creation:
Insert data into the Table:
Output:
Here in the above table, we have inserted animal names which has ‘a’ string in it. But we have misplaced the ‘a’ with ‘s’.
Bear ->besr and so on. Now let us replace the ‘s’ substring to ‘a’ from the column “animal_name”.
Output:
From the above output, we could observe that the ‘s’ is replaced with the ‘a’.
To update the column values we can do the below:
Now we have updated the animal replace column with the required replacement. Let us see is the data is updated. Updated data output should be in the below manner: –
Output:
We could see that the data has been updated in the table as well.
Replace function is not a case- sensitive. Hence ‘s’ and ‘S’ are same. Let us see an example for the same.
Here above example is to replace the ‘s’ to ‘m’. As replace is not case sensitive all the ‘s’ string will be replace with ‘m’.
Output:
Example
Now let us consider a few examples for the replace function and its output as below: –
Example #1
Output:
Example #2
Now let us consider the table of students along with their phone number.
Table Creation:
Output:
Now let us replace the ‘(‘ and ‘)’ with none.
Output:
To update the change into the table:
Output:
Recommended Articles
We hope that this EDUCBA information on “SQL Server Replace” was beneficial to you. You can view EDUCBA’s recommended articles for more information.