SQL Server pad右脚本中的原因不明的运算符优先级

As part of a request to generate a zero padded unique identifer I created what I thought would be a simple statement

作为生成零填充唯一标识符的请求的一部分,我创建了我认为简单的语句

right('00000000' + convert(char(6),ID),6)

However, this did not turn out as zero padded characters at all. Further investigation reveals all is not as I would have expected. See:

但是,这根本不是零填充字符。进一步的调查显示一切都不像我预期的那样。看到:

drop table #test 
go
select --top 30
    right('00000000' + convert(varchar(6),ID),6) varcharPadRight, --results in varchar(6) in tempdb
    right('00000000' + convert(char(6),ID),6) charPadRight, --results in varchar(6) in tempdb
    right('00000000' + convert(char(6),ID),20) charPadRight20, --results in varchar(14) in tempdb
    right('00000000' + convert(varchar(6),ID),20) vcharPadRight20 --results in varchar(14) in tempdb
into #test
from requestidentifier aTableWithAnIntIdentityColumn
where aTableWithAnIntIdentityColumn.ID in (1,100,1000)
go
select * from #test

select left(so.name, 5) name,sc.name, sc.xtype, sc.length from tempdb..sysobjects so inner join tempdb..syscolumns sc on so.id = sc.id where so.name like '%test%'

The results of this are:

结果如下:

varcharPadRight charPadRight charPadRight20 vcharPadRight20
--------------- ------------ -------------- ---------------
000100          100          00000000100    00000000100
001000          1000         000000001000   000000001000
000001          1            000000001      000000001

and

tableName colName        xtype length
--------- -------------- ----- ------
#test     varcharPadRigh 167   6
#test     charPadRight   167   6
#test     charPadRight20 167   14
#test     vcharPadRight2 167   14

Where an xtype of 167 is a varchar.

其中xtype为167是varchar。

Is there anybody that can explain the ordering of the operations that would cause these (to me) unexpected results?

是否有人可以解释导致这些(对我而言)意外结果的操作顺序?

(This behaviour is consistent in SQL Server 2005 and 2008)

(此行为在SQL Server 2005和2008中是一致的)

2 个解决方案

#1

The simple explanation is that the char data type has spaces on the right to make it the length of the variable. So, if you have a char(6) and set it to '3' the value in the variable will actually be 3-space-space-space-space-space. That's a 3 followed by 5 spaces to make the total length = 6 characters.

简单的解释是char数据类型在右边有空格,使其成为变量的长度。因此,如果你有一个char(6)并将其设置为'3',那么变量中的值实际上将是3空间空间空间空间。这是一个3后跟5个空格,使总长度= 6个字符。

When you add 6 zero's to the left of the string, SQL is doing some data type conversions. Hard coding a string will result in a varchar, so '000000' will have the data type varchar(6). When you append a char and a varchar, the result is a varchar with the lengths combined.

当您在字符串的左侧添加6个零时,SQL正在进行一些数据类型转换。对字符串进行硬编码将导致varchar,因此'000000'将具有数据类型varchar(6)。附加char和varchar时,结果是一个长度合并的varchar。

'000000' + Convert(Char(6), int)
VarChar(6) + Char(6)
varchar(12)

The Char(6) part will still have the spaces padded on the right of the data, so when you take the 6 right most characters, you are getting the spaces.

Char(6)部分仍然会在数据的右侧填充空格,因此当您获取最右边的6个字符时,您将获得空格。

varchar doesn't pad with spaces on the end so it works exactly as you would expect it to.

varchar不会在末尾填充空格,因此它的工作方式与您期望的完全相同。

PROOF:

Declare @ID Int
Set @Id = 3

-- Data type after converting to char (results in char(6))
SELECT SQL_VARIANT_PROPERTY(Convert(Char(6), @id), 'BaseType') As DatType, 
       SQL_VARIANT_PROPERTY(Convert(Char(6), @id), 'MaxLength') As Length,
       Convert(Char(6), @id)

-- data type of hard coded string (Results in varchar(6))
SELECT SQL_VARIANT_PROPERTY('000000', 'BaseType') As DatType, 
       SQL_VARIANT_PROPERTY('000000', 'MaxLength') As Length,
       '000000'

-- data type of varchar concatenate char (Results in varchar(12))
SELECT SQL_VARIANT_PROPERTY('000000' + Convert(Char(6), @id), 'BaseType') As DataType,
       SQL_VARIANT_PROPERTY('000000' + Convert(Char(6), @id), 'MaxLength') As Length,
       '000000' + Convert(Char(6), @id)

-- data type of the result (results in varchar(6))
SELECT SQL_VARIANT_PROPERTY(Right('000000' + Convert(Char(6), @id), 6), 'BaseType') As DataType,
       SQL_VARIANT_PROPERTY(Right('000000' + Convert(Char(6), @id), 6), 'MaxLength') As Length,
       Right('000000' + Convert(Char(6), @id), 6)

#2

Converting an int to a char(6) is left aligned, so

将int转换为char(6)是左对齐的,所以

   int -> char(6) -> '000' + char(6) -> right('000' + char(6))
    1  -> 1______ -> '0001_____'     -> '1_____'
    10 -> 10_____ -> '00010____'     -> '10____'
    etc

Thus an rtrim into the code will give the expected results e.g.

因此,代码中的rtrim将给出预期的结果,例如

right('00000000' + rtrim(convert(char(6),ID)),6)

#1

'000000' + Convert(Char(6), int)
VarChar(6) + Char(6)
varchar(12)