How do I remove the excess white space from the values in my SQL table?

Question

still kinda new to SQL.

My problem is this, say I created a table with one of its columns at nchar(200). This column contains the value 'Logged in.'. Every time I called it from the front-end, it returns 'Logged in '. Is there any way to remove the white space within the table? Or is there some setting that will remove this excess white space? I want it to return 'Logged in.' only. L/Rtrim don't seem to work.

Thanks

It returns 'Logged in' with enough trailing white spaces to make up 200 chars. (I am guessing 200 chars, I didn't count.) — Chris, Jul 07 '16 at 08:39
Can you confirm your RDBMS? I assumed SQL Server but just noticed you never stated that. — Martin Smith, Jul 07 '16 at 09:05

Martin Smith · Accepted Answer · 2016-07-07T09:01:22.177

The first thing you should do is change the datatype of the column to nvarchar(200),

The char and nchar datatypes are intended for data that is more or less fixed length. Your example shows that you are not dealing with fixed length 200 character strings.

Currently each instance of the 'Logged in' string takes 400 bytes. Stored as nvarchar it will take 20 bytes (and 11 as varchar).

Then you can RTRIM in an UPDATE statement to remove the trailing spaces.

NB: In some circumstances you might find that creating a new table from scratch actually works better than the above as the insert can be minimally logged and it doesn't copy the data once and then trim it. It also ensures that no space is consumed by the dropped column. You can use the SSMS table designer to generate the script for this and then add a RTRIM to the result for something like the below.

SET XACT_ABORT ON;
BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_YourTable
  (
     /*Other columns*/
     YourColumn NVARCHAR(400) NULL
  )

GO

INSERT INTO dbo.Tmp_YourTable WITH (TABLOCKX)
            (YourColumn)
SELECT /*Other columns*/ 
       RTRIM(CONVERT(NVARCHAR(400), YourColumn))
FROM   dbo.YourTable WITH (HOLDLOCK TABLOCKX)

DROP TABLE dbo.YourTable

GO

EXECUTE sp_rename
  N'dbo.Tmp_YourTable',
  N'YourTable',
  'OBJECT'

GO

COMMIT

How do I remove the excess white space from the values in my SQL table?

1 Answers1