How to update the database table by removing trailing spaces from one of the columns?

Question

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?

Oleg Dok · Accepted Answer · 2012-02-07 06:38:28Z

1

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.

edited Feb 7, 2012 at 6:38

answered Feb 7, 2012 at 6:20

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fawadafr Over a year ago

thanks for the feedback. Unfortunately, it did not work. I still have spaces in the [doc_path] field. As you can see my script above, the size of the field is 256. After reviewing some records, I realized the size of empty spaces are equal the text entered (e.g. /Documents/Procedures/CR/pdf/CR007.pdf) minus 256 (the total size of the field).

Oleg Dok Over a year ago

Yes, but you noticed new type of column - it simply must work without doubt

Collectives™ on Stack Overflow

How to update the database table by removing trailing spaces from one of the columns?

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related