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

  • 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

  • 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!

  • @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

  • 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.

  • @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

  • 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!

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

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

  • 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

  • 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!

  • 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]

  • 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.

  • 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

  • @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

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

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