删除字段内容中的尾随空白

发布于 2024-08-14 02:57:45 字数 255 浏览 8 评论 0原文

我正在使用 SQL Server MSDE 2000。我有一个名为 notes 的 nvarchar(65) 类型字段。

所有记录中的内容都是“Something”，内容后面有一个额外的空格（为了清晰起见，用引号引起来）。我使用了以下命令。

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

但这不起作用。有没有其他方法可以做到这一点？

原文

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

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夜清冷一曲。 2024-08-21 02:57:45

您确定查询不起作用吗？尝试：

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

TOP 100 将结果限制为前 100 行，足以了解输出中是否确实存在空格。如果有，并且 RTRIM/LTRIM 没有删除它 - 那么您没有处理空白字符。在这种情况下，请尝试：

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

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

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

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

回复收藏 0 原文

小忆控 2024-08-21 02:57:45

...或者您实际上可以复制/粘贴空白“ ” （空格）作为查询替换语句的结果出现在字段末尾，并从那里更新所有内容。

update TABLE1
set notes = replace(notes, ' ', '')

... OR you could literally just copy/paste the blank ' ' (space) at the end of a field as a result of your query into your replace statement and update everything from there.

update TABLE1
set notes = replace(notes, ' ', '')

回复收藏 0 原文

又爬满兰若 2024-08-21 02:57:45

如果您需要修剪所有列中的所有空格，您可以使用此脚本动态执行此操作：

--Just change table name
declare @MyTable varchar(100)
set @MyTable = 'MyTable'

--temp table to get column names and a row id
select column_name, ROW_NUMBER() OVER(ORDER BY column_name) as id into #tempcols from INFORMATION_SCHEMA.COLUMNS 
WHERE   DATA_TYPE IN ('varchar', 'nvarchar') and TABLE_NAME = @MyTable

declare @tri int
select @tri = count(*) from #tempcols
declare @i int
select @i = 0
declare @trimmer nvarchar(max)
declare @comma varchar(1)
set @comma = ', '

--Build Update query
select @trimmer = 'UPDATE [dbo].[' + @MyTable + '] SET '

WHILE @i <= @tri 
BEGIN

    IF (@i = @tri)
        BEGIN
        set @comma = ''
        END
    SELECT  @trimmer = @trimmer + CHAR(10)+ '[' + COLUMN_NAME + '] = LTRIM(RTRIM([' + COLUMN_NAME + ']))'+@comma
    FROM    #tempcols
    where id = @i

    select @i = @i+1
END

--execute the entire query
EXEC sp_executesql @trimmer

drop table #tempcols

And just in case you need to TRIM all spaces in all columns, you can use this script to do it dynamically:

--Just change table name
declare @MyTable varchar(100)
set @MyTable = 'MyTable'

--temp table to get column names and a row id
select column_name, ROW_NUMBER() OVER(ORDER BY column_name) as id into #tempcols from INFORMATION_SCHEMA.COLUMNS 
WHERE   DATA_TYPE IN ('varchar', 'nvarchar') and TABLE_NAME = @MyTable

declare @tri int
select @tri = count(*) from #tempcols
declare @i int
select @i = 0
declare @trimmer nvarchar(max)
declare @comma varchar(1)
set @comma = ', '

--Build Update query
select @trimmer = 'UPDATE [dbo].[' + @MyTable + '] SET '

WHILE @i <= @tri 
BEGIN

    IF (@i = @tri)
        BEGIN
        set @comma = ''
        END
    SELECT  @trimmer = @trimmer + CHAR(10)+ '[' + COLUMN_NAME + '] = LTRIM(RTRIM([' + COLUMN_NAME + ']))'+@comma
    FROM    #tempcols
    where id = @i

    select @i = @i+1
END

--execute the entire query
EXEC sp_executesql @trimmer

drop table #tempcols

回复收藏 0 原文

~没有更多了~