如何通过删除其中一列的尾随空格来更新数据库表？

发布于 2025-01-03 17:07:00 字数 926 浏览 1 评论 0原文

我正在尝试编写一些 SQL 语句，以便通过删除其中一列中的所有空格来更新整个数据库表。

这是数据库设计：

<!-- language: lang-sql -->
/****** Object:  Table [dbo].[document_control]    Script Date: 02/06/2012 21:02:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[document_control](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [doc_number] [nchar](10) NOT NULL,
    [doc_title] [nchar](256) NOT NULL,
    [co_num] [int] NULL,
    [co_date] [date] NULL,
    [doc_path] [nchar](256) NULL,
    [doc_revision] [int] NULL,
    [doc_family] [nvarchar](5) NULL,
 CONSTRAINT [PK_document_control] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

我在 [doc_path] 列中有很多空格。我知道我可以轻松使用 LTRIM() 函数来删除空格；但是，如何编写 SQL 语句才能更新整个数据库呢？

原文

I am trying to write some SQL statement so I can update my entire database table by removing all the spaces from one of its columns.

Here is the database design:

<!-- language: lang-sql -->
/****** Object:  Table [dbo].[document_control]    Script Date: 02/06/2012 21:02:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[document_control](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [doc_number] [nchar](10) NOT NULL,
    [doc_title] [nchar](256) NOT NULL,
    [co_num] [int] NULL,
    [co_date] [date] NULL,
    [doc_path] [nchar](256) NULL,
    [doc_revision] [int] NULL,
    [doc_family] [nvarchar](5) NULL,
 CONSTRAINT [PK_document_control] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

I have lots of spaces in the [doc_path] column specifically. I know I can easily use LTRIM() function to remove the spaced; however, how can I write a SQL statement so I can update the entire database?

分享到QQ

分享到微博

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

发布评论

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

无声无音无过去 2025-01-10 17:07:00

您应该像这样更改列类型：

ALTER TABLE [dbo].[document_control] MODIFY COLUMN [doc_path] [nvarchar](256)
GO
UPDATE [dbo].[document_control] SET 
  [doc_path] = RTRIM([doc_path])
GO

并且可以选择对文档标题和编号进行相同的更改。

You should change your column type like this:

ALTER TABLE [dbo].[document_control] MODIFY COLUMN [doc_path] [nvarchar](256)
GO
UPDATE [dbo].[document_control] SET 
  [doc_path] = RTRIM([doc_path])
GO

And optionally the same for doc title and number.

回复收藏 0 原文

~没有更多了~