December 12, 2016 at 9:17 am
hello there,
I have noticed that when i tried to restore a sql server 2005 database to sql server 2014 some of the char fields insert a lot of spaces at the end and at the front of the column.
I use a lot of string functions to sort it out but i want to find out if that can be avoided when upgrading
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(namex, CHAR(9), ' '), CHAR(13), ' '), CHAR(10), ' ')))
Do you have any idea why this is happening ?
December 12, 2016 at 9:32 am
CHAR and NCHAR will always use the full amount of characters. if tehre aren't enough, then Whitespace will be added.
For example, on a CHAR(10):
'Home' would become: 'Home '
'House' would become 'House '
'Household' would become 'Household '
I would consider using VARCHAR/NVARCHAR instead, which will not include Whitespace.
Whitespace at the front, however, would mean that this is already present. Neither VARCHAR or CHAR ignore leading whitespace.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 12, 2016 at 9:39 am
The behaviour and handling of characters is exactly the same on both 2005 and 2014, in fact it hasn't really changes any since SQL Server 6.5, upgrade is not going to change anything here.
😎
As already mentioned on this thread, fixed size character data types will always take the full space/number of characters and pad the value with (N)CHAR(32), if leading or trailing spaces are not wanted then use the variable length data types.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply