While working on a client project we noticed that all string columns has been set to varchar columns on the SQL database.  This was a small issue as all columns now contains empty spaces when not used and we wanted to remove white space characters from all columns in SQL database.

I personally don’t like varchar and prefer to use nvarchar, but as this was the case I didn’t really had a choice.  We are running a Web API 2 web service that communicates to that SQL database via LINQ.

When returning the Json data to the mobile device all the empty spaces was present and the data was unreadable.

Resolution

You can either trim all items before passing it back or you can do the following.

Firstly rename all the columns to nvarchar.  You will notice that all empty space are still present and in order to remove this you can run the following code.  This code creates an update statement with all the columns matching the input, in this case nvarchar and trims all the white spaces from the string value.

DECLARE @SQL VARCHAR(MAX)
DECLARE @TableName NVARCHAR(128)
SET @TableName = 'TheTableNameToRunThisOn'
 
SELECT 
    @SQL = COALESCE(@SQL + ',[', '[') + COLUMN_NAME + ']=RTRIM([' + COLUMN_NAME + '])'
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_NAME = @TableName
    AND DATA_TYPE = 'nvarchar'
 
SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL
EXECUTE(@SQL)

After you have executed this on your table you can move to other tables that require this and just change all varchar’s to nvarchar and run the script with the new table name in.

If you have other suggestions on how to handle varchar values whitespaces, please feel free to share.