February 7, 2012 at 10:14 am
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
February 7, 2012 at 10:21 am
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
February 7, 2012 at 10:27 am
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
February 7, 2012 at 10:32 am
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.
February 7, 2012 at 10:42 am
@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
February 7, 2012 at 10:46 am
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
February 7, 2012 at 10:47 am
I suggest you use the NVARCHAR datatype to be consistent with the doc_family column.
You should "ALTER COLUMN" rather than "MODIFY COLUMN".
February 7, 2012 at 10:58 am
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
February 7, 2012 at 11:18 am
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
February 7, 2012 at 11:30 am
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))
February 7, 2012 at 11:31 am
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.
February 7, 2012 at 11:40 am
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
February 7, 2012 at 11:43 am
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