删除字段内容中的尾随空格 [英] Remove trailing empty space in a field content

问题描述

我使用的是 SQL Server MSDE 2000.我有一个名为 notes 的字段，其类型为 nvarchar(65).

I am using SQL server MSDE 2000. I have a field called notes of type nvarchar(65).

在所有记录中，内容是Something"，内容后有一个额外的空格(为清楚起见而引用).我使用了以下命令.

The content is 'Something ' with an extra space after the content (quotes for clarity) in all the records. I used the following command.

UPDATE TABLE1 
   SET notes = RTRIM(LTRIM(notes))

但它不起作用.有什么替代方法吗?

But it does not work. Is there any alternate way to do it?

推荐答案

您确定查询不起作用吗?试试:

Are you sure the query isn't working? Try:

SELECT TOP 100 '~'+ t.notes +'~'
  FROM TABLE1 t

TOP 100 会将结果限制在前 100 行，足以让您了解输出中是否真的有空格.如果有，并且 RTRIM/LTRIM 没有删除它 - 那么你不是在处理空白字符.在这种情况下，请尝试:

TOP 100 will limit the results to the first 100 rows, enough to get an idea if there's really a space in the output. If there is, and RTRIM/LTRIM is not removing it - then you aren't dealing with a whitespace character. In that case, try:

UPDATE TABLE1
  SET notes = REPLACE(notes, 
                      SUBSTRING(notes, PATINDEX('%[^a-zA-Z0-9 '''''']%', notes), 1), 
                      '')
WHERE PATINDEX('%[^a-zA-Z0-9 '''''']%', notes) <> 0

这篇关于删除字段内容中的尾随空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！