Concatenation of two char variable in sql

Question

0.00/5 (No votes)

See more:

SQL-server-2000

, +

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 ..

SQL

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

after executing query it will give result ...

VB

CharSpaceUsed1
20
VarCharSpaceUsed2
20

till yet all is ok

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

SQL

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

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 ...

Posted 1-Dec-14 18:55pm

GDdixit

Add a Solution

4 solutions

Solution 2

C#

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.

see this, you'll get the answer..
http://msdn.microsoft.com/en-us/library/ms173486.aspx[^]
http://msdn.microsoft.com/en-us/library/ms190329.aspx[^]

Posted 1-Dec-14 19:07pm

/\jmot

Solution 3

Its beacuse you have delared as Char.
In SQL Char data type is fixed length data type.iots better you can use Varchar datatype See your query below :

SQL

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

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

Posted 1-Dec-14 19:08pm

syed shanu

Comments

GDdixit 2-Dec-14 1:33am

no , char have not fixed length , its' have fixed bytes that it taken ..

if we find it's length individually by using len function it will give 9 and 7 respectively for @firstname and @lastname ...

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-12-01T20:23:00

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.

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)

DamithSL · Accepted Answer · 2014-12-01T18:58:00

Solution 1

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

refer :
DATALENGTH [^]
LEN [^]

Posted 1-Dec-14 18:58pm

DamithSL

Updated 1-Dec-14 19:17pm

v5

Comments

/\jmot 2-Dec-14 1:08am

+5

/\jmot 2-Dec-14 1:08am

i didn't see your answer before posting mine.

GDdixit 2-Dec-14 1:31am

hi , you have specified here that length of @firstname is =20. and for space it's one and for @lastname it's 7 ...

but if we use len function for finding their lenght individually than ...

SELECT LEN(@FirstName) CharSpaceUsed,
LEN(@LastName) VarCharSpaceUsed

it will gives length of @firstname is 9 and for @lastname is 7
and as you specified length above for @firstname is 20 . it's the bytes used by this variable ...

it must be 9+1+7=17

DamithSL 2-Dec-14 1:37am

No,
LEN(@FirstName) + LEN(' ') + LEN(@LastName) = 9+1+7= 17
that is not equal to
LEN(@FirstName + ' ' + @LastName)
note that below expression having string with some white spaces after first name and those will not ignored by using len function
@FirstName + ' ' + @LastName
equal to
"ruchrmitl........kolkata........"
dots means empty space
Len function will get only "ruchrmitl........kolkata" part, neglect trailing spaces. hope this will help you to understand what is the reason.

Maciej Los 2-Dec-14 2:25am

You're right!
I explained it in my answer ;)

GDdixit 2-Dec-14 2:23am

it's working fine if change datatypes to varchar ....
it does not take any extra spaces in case of varchar...

GDdixit 2-Dec-14 2:25am

how empty spaces come in @firstname, although i didn't give spaces ?

Maciej Los 2-Dec-14 2:25am

+5

DamithSL 2-Dec-14 3:43am

Thank you, Maciej