sql server 替换_SQL Server替换功能–全面指南

sql server 替换

Hello, readers! In this article, we will be understanding the working of SQL Server Replace function.

您好，读者们！ 在本文中，我们将了解SQL Server Replace函数的工作原理 。

Let us get started!

让我们开始吧！

SQL Server替换功能的工作 (Working of the SQL Server Replace function)

The name ‘Replace’ itself defines its meaning. Yes! You have guessed it right.

名称“替换”本身定义了它的含义。 是! 您猜对了。

SQL Server Replace function enables us to replace a string or a portion of string(substring) and all its occurrences with a new string.

SQL Server Replace function使我们能够用新字符串替换字符串或部分字符串（子字符串）及其所有出现的位置。

In other programming languages, such as Java or C++, we do have defined functions to replace certain characters or sub-strings with other(new) string.

在其他编程语言（例如Java或C ++）中，我们确实定义了函数，以用其他（新）字符串替换某些字符或子字符串。

Let us understand the concept of Replace function with the help of an example.

让我们借助示例来了解替换功能的概念。

Consider an Online ticket booking system. If we wish to place and open slots for customers to book tickets only for ‘Pune’, we will have to replace the current city status and all its occurrences to ‘Pune’. This is when the REPLACE function comes into the picture. Using the function, we can replace every occurrence of the current city with ‘Pune’.

考虑一个在线票务预订系统。 如果我们希望放置和开放供客户预订仅适用于“浦那”的门票的票位，我们将必须将当前的城市状态及其所有出现的位置替换为“浦那”。 这是当REPLACE功能进入画面时。 使用该功能，我们可以用“浦那”代替当前城市的所有出现。

Having understood the working, let us now understand the structure of Replace function.

了解了工作原理后，现在让我们了解Replace函数的结构。

REPLACE函数的语法 (Syntax of REPLACE function)

SQL Server Replace function replaces every occurrence or presence of the mentioned string/substring with the new/set string.

SQL Server替换功能用新的/设置的字符串替换每次出现或存在的提到的字符串/子字符串。

REPLACE(string/substring, old_str, new_str)

• string/substring: The string within which the data would be replaced.
  字符串/子字符串：将在其中替换数据的字符串。
• old_str: The string to be replaced.
  old_str：要替换的字符串。
• new_str: The string value that replaces the old string.
  new_str：替换旧字符串的字符串值。

Moreover, the Replace function is case-insensitive.

此外，“替换”功能不区分大小写。

Let us now understand the implementation of Replace function with the help of some examples.

现在，让我们借助一些示例来了解Replace函数的实现。

通过示例实现替换功能 (Implementing Replace function through examples)

In the below example,we have replaced the word ‘Morning’ from the string ‘Good Morning’ with the new string ‘Evening’.

在下面的示例中，我们将字符串“ Good Morning”中的单词“ Morning”替换为新的字符串“ Evening”。

SELECT 
    REPLACE(
        'Good Morning.', 
        'Morning', 
        'Evening'
    ) output;

Output:

输出：

Good Evening.

Further, now we have replaced multiple occurrences of the string ‘Morning’ with ‘Evening’.

此外，现在我们将字符串“ Morning”的多次出现替换为“ Evening”。

SELECT 
    REPLACE(
        'Good Morning. This MORNING seems beautiful!!', 
        'Morning', 
        'Evening'
    ) output;

Moreover, it is clear from the above example that Replace function is totally case-insensitive.

此外，从以上示例可以清楚地看到，Replace函数完全不区分大小写。

Result:

结果：

Good Evening. This Evening seems beautiful!!

Now, we have created a table ‘Info’ and added values using the below command:

现在，我们创建了一个表“ Info”，并使用以下命令添加了值：

CREATE TABLE Info (
    id INT PRIMARY KEY,
    City VARCHAR (255) NOT NULL
); 
 
INSERT INTO Info (
   id,
   City
)
VALUES
    (
        1,
      '  Pune  '
    ),
    (
        2,
      '       Satara  '
    ),
    (
        3,
      ' California       '
    );
Select * from Info;

Output:

输出：

id	City
1	Pune
2	Satara
3	California

In the below example, we have replaced every occurrence of the character ‘a’ with ‘B’ in the column ‘City’, provided the data values of ‘City’ is not null.

在下面的示例中，我们假设在“城市”列中每次出现的字符“ a”都替换为“ B”，前提是“城市”的数据值不为空。

UPDATE
    Info
SET
    City = REPLACE(City,'a','B')
WHERE
    City IS NOT NULL;

Output:

输出：

结论 (Conclusion)

By this, we have come to the end of this topic. Feel free to comment below, in case you come across any question.

至此，我们到了本主题的结尾。 如果您遇到任何问题，请在下面发表评论。

For more such posts related to SQL Server, please do visit SQL Server JournalDev.

有关与SQL Server相关的更多此类帖子，请访问SQL Server JournalDev 。

参考资料 (References)

• SQL Server Replace function — Documentation
  SQL Server替换功能-文档

翻译自: https://www.journaldev.com/42230/sql-server-replace-function

sql server 替换