如何使用 LTRIM/RTRIM 搜索和替换前导/尾随空格? [英] How can I use LTRIM/RTRIM to search and replace leading/trailing spaces?

问题描述

我正在尝试从填充了价格的 NVARCHAR(MAX) 列中清除前导和尾随空格(使用 NVARCHAR，因为数据从具有奇数字符的多个操作系统导入).

I'm in the process of trying to clear out leading and trailing spaces from an NVARCHAR(MAX) column that is filled with prices (using NVARCHAR due to data importing from multiple operating systems with odd characters).

此时我有一个 t-sql 命令可以从静态价格中删除前导/尾随空格.然而，当谈到利用这个相同的命令来删除所有价格时，我很难过.

At this point I have a t-sql command that can remove the leading/trailing spaces from static prices. However, when it comes to leveraging this same command to remove all prices, I'm stumped.

这是我用来删除特定价格的静态脚本:

Here's the static script I used to remove a specific price:

UPDATE *tablename* set *columnname* = LTRIM(RTRIM(2.50)) WHERE cost = '2.50 ';

这是我尝试删除所有尾随空格的方法:

Here's what I've tried to remove all the trailing spaces:

UPDATE *tablename* set *columnname* LIKE LTRIM(RTRIM('[.]')) WHERE cost LIKE '[.] ';

我也为随机字符尝试了不同的 % 变体，但此时我正在旋转我的轮子.

I've also tried different varations of the % for random characters but at this point I'm spinning my wheels.

我希望实现的是运行一个简单的命令，在不修改任何实际列数据的情况下删除该列每个单元格中的所有前导和尾随空格.

What I'm hoping to achieve is to run one simple command that takes off all the leading and trailing spaces in each cell of this column without modifying any of the actual column data.

推荐答案

要删除左/右空格，请使用 LTRIM/RTRIM.你有什么

To remove spaces from left/right, use LTRIM/RTRIM. What you had

UPDATE *tablename*
   SET *columnname* = LTRIM(RTRIM(*columnname*));

本来可以处理所有行.为了在不需要更新的情况下尽量减少更新，更新代码不变，但 WHERE 子句中的 LIKE 表达式将是

would have worked on ALL the rows. To minimize updates if you don't need to update, the update code is unchanged, but the LIKE expression in the WHERE clause would have been

UPDATE [tablename]
   SET [columnname] = LTRIM(RTRIM([columnname]))
 WHERE 32 in (ASCII([columname]), ASCII(REVERSE([columname])));

注意:32是空格字符的ascii码.

Note: 32 is the ascii code for the space character.

这篇关于如何使用 LTRIM/RTRIM 搜索和替换前导/尾随空格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！