sql trim函数_SQL TRIM函数

最新推荐文章于 2024-05-21 15:48:04 发布

culuo4781

最新推荐文章于 2024-05-21 15:48:04 发布

阅读量9.9k

点赞数 2

文章标签：字符串 python java 数据库 mysql

原文链接：https://www.sqlshack.com/sql-trim-function/

版权

sql trim函数

In this article, we will review the new SQL TRIM function in SQL Server 2017 onwards as well as providing some information on strings functions that pre-date it like LTRIM AND RTRIM.

在本文中，我们将回顾SQL Server 2017及更高版本中的新SQL TRIM函数，并提供有关早于它的字符串函数（如LTRIM和RTRIM）的一些信息。

SQL Developers usually face issueS with spaceS at the beginning and/or end of a character string. We may need to trim leading and trailing characters from a character string. We might need to do string manipulations with SQL functions as well. Suppose we want to remove spaces from the trailing end of a string, we would need to use SQL LTRIM and RTRIM functions until SQL Server 2016.

SQL开发人员通常在字符串的开头和/或结尾面临带有空格的问题。我们可能需要修剪字符串中的前导和尾随字符。我们可能还需要使用SQL函数进行字符串操作。假设我们要从字符串的尾部删除空格，则需要在SQL Server 2016之前使用SQL LTRIM和RTRIM函数。

In SQL Server 2017, we get a new built-in function to trim both leading and trailing characters together with a single function. SQL TRIM function provides additional functionality of removing characters from the specified string. We can still use RTRIM and LTRIM function with SQL Server 2017 as well. Let’s explore these functions with examples.

在SQL Server 2017中，我们获得了一个新的内置函数，可以使用单个函数修剪前导字符和尾随字符。 SQL TRIM函数提供了从指定字符串中删除字符的附加功能。我们仍然可以在SQL Server 2017中使用RTRIM和LTRIM函数。让我们通过示例探索这些功能。

SQL LTRIM函数 (SQL LTRIM function)

It removes characters from beginning (Starting from the left side) of the specified string. In the following query, we have white space before and after the string. We need to remove space from the left side of the string using the LTRIM function.

它从指定字符串的开头（从左侧开始）删除字符。在以下查询中，字符串前后有空格。我们需要使用LTRIM函数从字符串的左侧删除空格。

We use the SQL DATALENGTH() function to calculate data length in bytes before and after using SQL LTRIM function.

在使用SQL LTRIM函数之前和之后，我们使用SQL DATALENGTH（）函数来计算数据长度（以字节为单位）。

DECLARE @String VARCHAR(26)= '     Application       ';
SELECT @String as OriginalString, 
       LTRIM(@String) AS StringAfterTRIM, 
       DATALENGTH(@String) AS 'DataLength String (Bytes)', 
       DATALENGTH(LTRIM(@String)) AS 'DataLength String (Bytes) After TRIM';

Data String size in Bytes for Original String: 24
原始字符串的数据字符串大小（以字节为单位）：24
Data String size in Bytes after SQL LTRIM: 18
SQL LTRIM之后的数据字符串大小（以字节为单位）：18

SQL RTRIM函数 (SQL RTRIM function)

It removes characters from the end ((Starting from the right side) of the specified string. Let’s execute the following query to look at the effect of SQL RTRIM function.

它从指定字符串的末尾（（从右侧开始）删除字符。让我们执行以下查询以查看SQL RTRIM函数的效果。

DECLARE @String VARCHAR(26)= '     Application         ';
SELECT @String as OriginalString, 
       RTRIM(@String) AS StringAfterTRIM, 
       DATALENGTH(@String) AS 'DataLength String (Bytes)', 
       DATALENGTH(RTRIM(@String)) AS 'DataLength String (Bytes) After TRIM';

Data String size in Bytes for Original String: 24
原始字符串的数据字符串大小（以字节为单位）：24
Data String size in Bytes after SQL RTRIM: 16
SQL RTRIM之后的数据字符串大小（以字节为单位）：16

SQL Server 2017中的TRIM函数 (TRIM function in SQL Server 2017)

Before SQL Server 2017, if we need to remove both Leading and Training spaces, we need to use both LTRIM and RTRIM function. In the following query, we used both LTRIM and RTIM functions with a string variable.

在SQL Server 2017之前，如果需要同时删除Leading和Training空间，则需要同时使用LTRIM和RTRIM函数。在以下查询中，我们将LTRIM和RTIM函数与字符串变量一起使用。

DECLARE @String VARCHAR(26)= '     Application         ';
SELECT @String as OriginalString, 
       LTRIM(RTRIM(@String)) AS StringAfterTRIM, 
       DATALENGTH(@String) AS 'DataLength String (Bytes)', 
       DATALENGTH(LTRIM(RTRIM(@String))) AS 'DataLength String (Bytes) After TRIM';

In the following screenshot, we can see it removes both leading and trailing space from the string.

在下面的屏幕截图中，我们可以看到它删除了字符串中的前导和尾随空格。

Data String size in Bytes for Original String: 24
原始字符串的数据字符串大小（以字节为单位）：24
Data String size in Bytes after SQL LTRIM: 11
SQL LTRIM之后的数据字符串大小（以字节为单位）：11

Data String size in Bytes for Original String: 25
原始字符串的数据字符串大小（以字节为单位）：25
Data String size in Bytes after SQL LTRIM and RTRIM: 11
SQL LTRIM和RTRIM之后的数据字符串大小（以字节为单位）：11

We need to use two functions to remove the spaces from the string. We can use a single instance of a TRIM function in SQL Server 2017 onwards to trim both leading and trailing characters, let’s explore the TRIM function in a further section of this article.

我们需要使用两个函数从字符串中删除空格。我们可以在SQL Server 2017及更高版本中使用TRIM函数的单个实例来修剪前导和尾随字符，让我们在本文的下一部分中探索TRIM函数。

TRIM函数的语法 (The Syntax of the TRIM Function)

TRIM ([Trimcharacters FROM] string)

TrimCharacter: We can specify the character to remove from the string. By default, SQL Server removes spaces from both sides of a string TrimCharacter ：我们可以指定要从字符串中删除的字符。默认情况下，SQL Server从字符串的两侧删除空格
String: We need to specify the string that we want to trim 字符串：我们需要指定要修剪的字符串

Let’s explore SQL TRIM function with examples.

让我们通过示例探索SQL TRIM函数。

TRIM领导和培训空间： (TRIM Leading and Training spaces:)

By default, Trim function removes the space from both sides of a string. In the following query, we have to define a string with space on both sides of the string.

默认情况下，Trim函数会删除字符串两侧的空格。在下面的查询中，我们必须定义一个字符串，字符串的两边都有空格。

Execute this query:

执行此查询：

DECLARE @String VARCHAR(24)= '      Application       ';
SELECT @String as OriginalString, 
       TRIM(@String) AS StringAfterTRIM, 
       DATALENGTH(@String) AS 'DataLength String (Bytes)', 
       DATALENGTH(TRIM(@String)) AS 'DataLength String (Bytes) After TRIM';

We did not specify any characters to remove in TRIM function. In the output, SQL TRIM function removes both leading and trailing space.

我们没有在TRIM函数中指定要删除的任何字符。在输出中，SQL TRIM函数将删除前导和尾随空格。

You can also notice the difference in string length before and after the TRIM function.

您还可以注意到TRIM函数前后字符串长度的差异。

Data String size in Bytes for Original String: 24
原始字符串的数据字符串大小（以字节为单位）：24
Data String size in Bytes after SQL TRIM: 11
SQL TRIM之后的数据字符串大小（以字节为单位）：11

TRIM字符串中的前导字符： (TRIM Leading characters from a string:)

In the previous example, we explored that SQL TRIM is the replacement of RTRIM and LTRIM and it eliminates to delete all space character before and after a string. We can remove the characters as well from the string using TRIM. Suppose we want to remove character ON from the string, we can specify it in TRIM function as per following script.

在前面的示例中，我们探讨了SQL TRIM是RTRIM和LTRIM的替代品，它消除了删除字符串前后的所有空格字符。我们也可以使用TRIM从字符串中删除字符。假设我们要从字符串中删除字符ON ，可以按照以下脚本在TRIM函数中指定它。

DECLARE @String VARCHAR(24)= 'Application';
SELECT @String as OriginalString, 
       TRIM('On' from @String) AS StringAfterTRIM, 
       DATALENGTH(@String) AS 'DataLength String (Bytes)', 
       DATALENGTH( TRIM('On' from @String) ) AS 'DataLength String (Bytes) After TRIM';

It checks the specified characters on both the leading and trailing side and removes the particular characters. In the string Application, we have character ON in the leading side. In the following screenshot, you can look at the output after TRIM function.

它在开头和结尾都检查指定的字符，并删除特定的字符。在字符串Application中，我们在开头将字符ON。在下面的屏幕快照中，您可以查看TRIM功能之后的输出。

Data String size in Bytes for Original String: 11
原始字符串的数据字符串大小（以字节为单位）：11
Data String size in Bytes after SQL TRIM: 9
SQL TRIM之后的数据字符串大小（以字节为单位）：9

Let’s look at one more example. This time we want to remove characters App from the leading side. We do not need to make any changes in the script. We can specify the character to remove using SQL TRIM, and it gives the required output.

让我们再看一个例子。这次我们要从前端删除字符App 。我们不需要在脚本中进行任何更改。我们可以使用SQL TRIM指定要删除的字符，并提供所需的输出。

DECLARE @String VARCHAR(24)= 'Application';
SELECT @String as OriginalString, 
       TRIM('APP' from @String) AS StringAfterTRIM, 
       DATALENGTH(@String) AS 'DataLength String (Bytes)', 
       DATALENGTH( TRIM('APP' from @String) ) AS 'DataLength String (Bytes) After TRIM';

Data String size in Bytes for Original String: 11
原始字符串的数据字符串大小（以字节为单位）：11
Data String size in Bytes after SQL TRIM: 8
SQL TRIM之后的数据字符串大小（以字节为单位）：8

In previous examples, we removed character either from the leading end or trailing end. If we have a character on both sides, it removes them as well.

在前面的示例中，我们从前端或后端删除了字符。如果我们两边都有角色，它也会将其删除。

In the following example, we want to remove character A that is on both sides of the string. Execute the script, and it removes the specified characters from both sides.

在下面的示例中，我们要删除字符串两侧的字符A。执行该脚本，然后从两侧删除指定的字符。

DECLARE @String VARCHAR(24)= 'ApplicationA';
SELECT @String as OriginalString, 
       TRIM('A' from @String) AS StringAfterTRIM, 
       DATALENGTH(@String) AS 'DataLength String (Bytes)', 
       DATALENGTH( TRIM('A' from @String) ) AS 'DataLength String (Bytes) After TRIM';

Data String size in Bytes for Original String: 12
原始字符串的数据字符串大小（以字节为单位）：12
Data String size in Bytes after SQL TRIM: 10
SQL TRIM之后的数据字符串大小（以字节为单位）：10

Suppose we have specified a character that is not present on the string. In the following example, we want to remove character A and C from leading or trailing end. We do not have character C on either side, so it does not remove it. It only removes character A and gives the output.

假设我们指定了字符串中不存在的字符。在下面的示例中，我们要从开头或结尾删除字符A和C。 我们两边都没有字符C，因此不会将其删除。它仅删除字符A并提供输出。

DECLARE @String VARCHAR(24)= 'ApplicationA';
SELECT @String as OriginalString, 
       TRIM('AC' from @String) AS StringAfterTRIM, 
       DATALENGTH(@String) AS 'DataLength String (Bytes)', 
       DATALENGTH( TRIM('AC' from @String) ) AS 'DataLength String (Bytes) After TRIM';

Data String size in Bytes for Original String: 12
原始字符串的数据字符串大小（以字节为单位）：12
Data String size in Bytes after SQL TRIM: 10
SQL TRIM之后的数据字符串大小（以字节为单位）：10

TRIM用于表中的记录 (TRIM for records in a table)

In the previous article, we used a variable to define a string and perform SQL TRIM operation on it. Usually, we require using TRIM on table records. Let’s use it in the following example.

在上一篇文章中，我们使用了变量来定义字符串并对其执行SQL TRIM操作。通常，我们要求在表记录上使用TRIM。在下面的示例中使用它。

In the following examples, it eliminated character R from the string at the leading and trailing end.

在以下示例中，它从字符串的开头和结尾删除了字符R。

Before and after of using SQL Server Trim function

We can use SQL TRIM function in Update statement as well. Suppose we want to update all records in Employee table and want to eliminate characters Manager from JobTitile.

我们也可以在Update语句中使用SQL TRIM函数。假设我们要更新Employee表中的所有记录，并要从JobTitile中删除字符Manager 。

UPDATE [AdventureWorks2017].[HumanResources].[Employee]
SET JobTitle = TRIM ('Manager' from 'Research and Development Manager');

It does not treat the Manager as a single word. Trim function checks for each character in the string and eliminates it.

它不会将Manager视为一个单词。修剪功能检查字符串中的每个字符并将其消除。

We can use comma (,) to separate each character as well.

我们也可以使用逗号（，）分隔每个字符。

UPDATE [AdventureWorks2017].[HumanResources].[Employee]
SET JobTitle  = TRIM('M,a,n,a,g,e,r' from 'Research and Development Manager');

It also does the same work and removes specific characters from the string at a leading and trailing end.

它也做同样的工作，并从前端和尾端删除字符串中的特定字符。

结论 (Conclusion)

In this article, we explored the SQL TRIM function with SQL Server 2017 and above. We also learned about the TRIM functions LTRIM and RTRIM available on or before SQL Server 2016 as well.

在本文中，我们使用SQL Server 2017及更高版本探索了SQL TRIM函数。我们还了解了SQL Server 2016或之前可用的TRIM函数LTRIM和RTRIM。

Depending on the version of SQL Server you are using, you should understand these functions and be familiar with the concepts. I hope you find this article useful. Feel free to provide feedback or ask questions in the comments below.

根据所使用SQL Server版本，您应该了解这些功能并熟悉这些概念。希望本文对您有所帮助。欢迎在下面的评论中提供反馈或提出问题。

翻译自: https://www.sqlshack.com/sql-trim-function/

sql trim函数

culuo4781

关注

2
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
sql trim函数_SQL TRIM函数

sql trim函数 In this article, we will review the new SQL TRIM function in SQL Server 2017 onwards as well as providing some information on strings functions that pre-date it like LTRIM AND RTRIM. 在...
复制链接

扫一扫