Home Dashboard Directory Help

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


 as By Design Help for as By Design

Sign in
to vote
Type: Bug
ID: 777253
Opened: 1/23/2013 5:20:21 AM
Access Restriction: Public
User(s) can reproduce this bug


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.
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
MS Bugreport.sql 1/23/2013 1 KB
antresdownP.csv 1/23/2013 2.05 MB