Peculiar behaviour when accessing similar data using functions RIGHT, LTRIM and RTRIM. - by Koen Devos

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 777253 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 1/23/2013 5:20:21 AM
Access Restriction Public


Database table :
CREATE TABLE [dbo].[footbel](
	[divisie] [varchar](50) NULL,
	[datum] [char](10) NULL,
	[hour] [datetime] NULL,
	[home] [varchar](100) NULL,
	[away] [varchar](100) NULL,
	[reshome] [smallint] NULL,
	[resaway] [smallint] NULL,
	[status] [char](3) NULL,
	[md] [varchar](50) NULL,
	[regnumberhome] [char](6) NULL,
	[regnumberaway] [char](6) NULL

The database is filled with a bulk insert from a csv file.
When I look up the table (actually I want to do an Update) I get the following results:

SELECT     TOP (200) regnumberhome, RIGHT('00000' + RTRIM(LTRIM(regnumberhome)), 5) AS regnrH, regnumberaway, RIGHT('00000' + RTRIM(LTRIM(regnumberaway)), 5) 
                      AS regnrA, RIGHT('00000' + RTRIM(LTRIM(regnumberaway)), 6) AS regnr6
FROM         footbel
(partial) result :

3855  	03855	9446 	9446	09446
9567  	09567	683  	0683	00683
9446  	09446	201  	0201	00201
683   	00683	2982 	2982	02982
7436  	07436	3554 	3554	03554
3182  	03182	30    	0030	00030
4801  	04801	599  	0599	00599
3554  	03554	9328 	9328	09328

In the second column right(rtrim(ltrim(regnumberhome)),5) is the (correct) Number, 5 digits padded 0 to the left of the first database column.

However, for the third column, the same sequence of right(rtrim(ltrim()),5) gives only 4 digits !
I have to add an extra digit (right(rtrim(ltrim()),6)) to get the 5 numbers back !!!

Any advice on the matter ?
Sign in to post a comment.
Posted by Microsoft on 2/7/2013 at 8:47 AM
Hi!, thank you for writing to Microsoft.

We took a look at this bug recently and determined this is not a product issue and are resolving it ‘by design’. As you have posted the comment yourself about the problem, the data for the last column contains CR + LF. The bulk insert however inserts the CR into the last column and the result is expected.

Sanjay Nagamangalam, SQL Server Manageability
Posted by Koen Devos on 1/24/2013 at 3:44 AM
The answer could be that the file does contain a CRLF at the end instead of only LF as the original file. Therefor the <CR> is added to the regnumberaway and as such it is different than the regnumberhome. The field doesn't show the <CR>character, but it is there.
So the bulk upload has to change to not include the ROWTERMINATOR = '0x0a' parameter.