在sql中连接两个char变量 [英] Concatenation of two char variable in sql

问题描述

我目前正在使用sql server 2012。



i已尝试过Concatenat两种char类型的变量...



DECLARE @FirstName Char（20）='ruchrmitl'，

@ LastName Char（20）='kolkata'



如果我们发现在db中占用了多少空间...比...

i am currently using "sql server 2012" .

i have tried an effort of Concatenat two char type of variable ...

DECLARE @FirstName Char(20) = 'ruchrmitl',
@LastName Char(20) = 'kolkata'

if we find how much space it has been taken in db ... than ..

SELECT DATALENGTH(@FirstName) CharSpaceUsed1,
DATALENGTH(@LastName) CharSpaceUsed2

执行查询后会给出结果...

after executing query it will give result ...

CharSpaceUsed1
20
VarCharSpaceUsed2
20

直到一切都好的







但是在连接之后如果我看到长度，那对我来说很惊讶...

till yet all is ok



but after concatenate if i saw length , it's surprising to me ...

SELECT  @FirstName + ' ' + @LastName AS Name,
 len(@FirstName + ' ' + @LastName) AS Length

它会给出结果......



姓名长度

ruchrmitl kolkata 28





它的长度如何变为28.



可以解释任何一个2 me，thanx in advanse ...

it will give result as ...

Name Length
ruchrmitl kolkata 28


how it's length become 28 .

can any one explain 2 me , thanx in advanse ...

推荐答案

char和varchar [ ^ ]数据类型：

There is a bit difference between char and varchar[^] data types:

char [ ( n ) ]
    Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The SQL-2003 synonym for char is character.

varchar [ ( n | max ) ]
    Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying.

所以，当你使用LEN [ ^ ]函数，它返回指定字符串表达式的字符数， 不包括尾随空白 ，其中意味着： LEN（@FirstName +''+ @LastName）= 28 ，因为 @FirstName 的尾随空白不是cutted，''（空格）长度等于1，长度 @LastName 等于7（尾随空格）被删除）！

声明：

LEN（@FirstName +''+ @LastName）

不等于：

LEN（@FirstName）+ LEN（''）+ LEN（@ LastName）

So, when you use LEN[^] function, it returns the number of characters of the specified string expression, excluding trailing blanks, which means: LEN(@FirstName + ' ' + @LastName)=28, because trailing blanks of @FirstName are not cutted, ' ' (space) length is equal to 1 and length of @LastName is equal to 7 (trailing blanks are removed)!
The statement:
LEN(@FirstName + ' ' + @LastName)
is not equal to:
LEN(@FirstName) + LEN(' ') + LEN(@LastName)

DATALENGTH返回用于表示任何表达式的字节数。所以在你的第一个sql语句中，20表示你的Char（20）字段中的20个字节。



但是 len（@FirstName +''+ @ LastName）给出指定字符串表达式的字符数，不包括尾随空格。



所以当你的@LastName尾随空格将被删除你使用LEN功能

DATALENGTH Returns the number of bytes used to represent any expression. so in your first sql statement 20 means 20 bytes in your Char(20) fields.

but len(@FirstName + ' ' + @LastName) give you number of characters of the specified string expression, excluding trailing blanks.

So your @LastName trailing spaces will be removed when you use LEN function

@FirstName + ' ' + @LastName = 20 + 1 + 7 =28

参考：

DATALENGTH [ ^ ]

LEN [ ^ ]

DATALENGTH :Returns the number of bytes used to represent any expression.
LEN :Returns the number of characters of the specified string expression, excluding trailing blanks.

看到这个，你会得到答案..

http：// msdn .microsoft.com / zh-CN / library / ms173486.aspx [ ^ ]

http：// msdn.microsoft.com/en-us/library/ms190329.aspx [ ^ ]

这篇关于在sql中连接两个char变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！