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

Question

Post reply

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

fawadafr

SSC Eights!

Points: 865
More actions
February 7, 2012 at 10:14 am

#251150

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:
/****** 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 by removing all the trailing spaced from the [doc_path] field?
Here is how the data appears when I retrieve some records:
id doc_number doc_title co_num co_date doc_path doc_revision doc_family
----------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ----------
1011 DC-001 Dc Procedure For Controlling Quality Records NULL 2009-06-26 /Documents/Procedures/pdf/DC001.pdf 10 DC
1012 DC-004 Dc Procedure For Processing Job Orders NULL 2009-12-09 /Documents/Procedures/pdf/DC004.pdf 7 DC
1013 DC-010 Document Control In Qcbd NULL 2009-05-01 /Documents/Procedures/pdf/DC010.pdf 3 DC
1014 DC-011 Digital Scanning Of Documents To Archive Folder NULL 2011-06-07 /Documents/Procedures/pdf/DC011.pdf 1 DC
(4 row(s) affected)
Fawad Rashidi
www.fawadafr.com

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

well the matching RTRIM function will remove trailing spaces...so i think you want something like this:

UPDATE [dbo].[document_control]

SET [doc_path] = LTRIM(RTRIM([doc_path]))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

fawadafr SSC Eights! Points: 865 More actions · Answer 2

@lowell:

Thanks for the feedback. Yes, I tried the LTRIM() and RTRIM() function but I still have the spaces at the end of [doc_title] field. Please see below:

id doc_number doc_title co_num co_date doc_path doc_revision doc_family

----------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ----------

1011 DC-001 Dc Procedure For Controlling Quality Records NULL 2009-06-26 /Documents/Procedures/pdf/DC001.pdf 10 DC

1012 DC-004 Dc Procedure For Processing Job Orders NULL 2009-12-09 /Documents/Procedures/pdf/DC004.pdf 7 DC

1013 DC-010 Document Control In Qcbd NULL 2009-05-01 /Documents/Procedures/pdf/DC010.pdf 3 DC

1014 DC-011 Digital Scanning Of Documents To Archive Folder NULL 2011-06-07 /Documents/Procedures/pdf/DC011.pdf 1 DC

(4 row(s) affected)

Fawad Rashidi
www.fawadafr.com

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 3

I believe the problem is that you are using CHAR fields rather than VARCHAR fields. CHAR fields are always padded to the specified number of characters. Convert to VARCHAR (or NVARCHAR) to allow variable-length values.

fawadafr SSC Eights! Points: 865 More actions · Answer 4

@Fahey:

Thanks for the feedback. I tried executing the following SQL statement but was unable to change the field type.

ALTER TABLE [dbo].[document_control] MODIFY COLUMN [doc_path] [varchar](256) NULL

Could you please help me how to change the field type?

Fawad Rashidi
www.fawadafr.com

Lowell SSC Guru Points: 323518 More actions · Answer 5

fawadafr (2/7/2012)
@Fahey:
Thanks for the feedback. I tried executing the following SQL statement but was unable to change the field type.
ALTER TABLE [dbo].[document_control] MODIFY COLUMN [doc_path] [varchar](256) NULL
Could you please help me how to change the field type?

is that syntax is for Oracleor mySQL?;

SQL Server is slightly different:

ALTER TABLE [dbo].[document_control] ALTER COLUMN [doc_path] [varchar](256) NULL

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 6

I suggest you use the NVARCHAR datatype to be consistent with the doc_family column.

You should "ALTER COLUMN" rather than "MODIFY COLUMN".

fawadafr SSC Eights! Points: 865 More actions · Answer 7

I just ran the script the ALTER the column. Here is the new table structure:

/****** Object: Table [dbo].[document_control] Script Date: 02/07/2012 09:55:54 ******/

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] [nvarchar](256) NULL,

[doc_revision] [int] NULL,

[doc_family] [nvarchar](256) 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

Why am I still getting extra spaces in the fields?

id doc_number doc_title co_num co_date doc_path doc_revision doc_family

----------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1011 DC-001 Dc Procedure For Controlling Quality Records NULL 2009-06-26 /Documents/Procedures/pdf/DC001.pdf 10 DC

1012 DC-004 Dc Procedure For Processing Job Orders NULL 2009-12-09 /Documents/Procedures/pdf/DC004.pdf 7 DC

1013 DC-010 Document Control In Qcbd NULL 2009-05-01 /Documents/Procedures/pdf/DC010.pdf 3 DC

1014 DC-011 Digital Scanning Of Documents To Archive Folder NULL 2011-06-07 /Documents/Procedures/pdf/DC011.pdf 1 DC

(4 row(s) affected)

Fawad Rashidi
www.fawadafr.com

Lowell SSC Guru Points: 323518 More actions · Answer 8

the existing data has trailing spaces; converting the column to varchar does not auto-trim the existing data.

now if you run our original update statement, you should see the difference.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ron McCullough SSC Guru Points: 63877 More actions · Answer 9

Unless it is extremely important to you now I suggest you leave things as they are. Why do I say that - well read the following:

http://msdn.microsoft.com/en-us/library/ms187403.aspx

Which in part states

In a future version of Microsoft SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Now you could execute a query to determine the length of the data in the column and based on the results, alter (reduce) the size of the column, which of course is not a complete solution, but may be of some use in your situation

SELECT MAX(LEN(Doc_title)),MIN(LEN(Doc_title))

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

sturner SSC-Insane Points: 22411 More actions · Answer 10

One of the reasons to use a char data type over a varchar is to avoid performance problems if the column gets updated some time after the row has been inserted.

If this column is updated after initial insert you may find that the resulting page splits and table fragmentation much worse that just using RTRIM() on the column when you need to use it.

The probability of survival is inversely proportional to the angle of arrival.

fawadafr SSC Eights! Points: 865 More actions · Answer 11

Lowell:

I am still having the space issue. Do you think I should export all the data and re-import them now the field properties are fixed? I only have about 500 records.

Here is the latest results:

id doc_number doc_title co_num co_date doc_path doc_revision doc_family

----------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ---------- -------------------------------------------------- ------------ ----------

1011 DC-001 Dc Procedure For Controlling Quality Records NULL 2009-06-26 /Documents/Procedures/pdf/DC001.pdf 10 DC

1012 DC-004 Dc Procedure For Processing Job Orders NULL 2009-12-09 /Documents/Procedures/pdf/DC004.pdf 7 DC

1013 DC-010 Document Control In Qcbd NULL 2009-05-01 /Documents/Procedures/pdf/DC010.pdf 3 DC

1014 DC-011 Digital Scanning Of Documents To Archive Folder NULL 2011-06-07 /Documents/Procedures/pdf/DC011.pdf 1 DC

(4 row(s) affected)

Fawad Rashidi
www.fawadafr.com

fawadafr SSC Eights! Points: 865 More actions · Answer 12

@bitbucket-25253:

Thanks for the feedback. Yes, it is extremely import for me to remove all the extra trailing spaces as they break the hyperlinks. The [doc_path] field contains the URL. Since it contains so many extra spaces, it breaks down the URL on the ASPX page.

Fawad Rashidi
www.fawadafr.com