Why spaces appear when using a cast for interger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Why spaces appear when using a cast for interger

Author

Topic

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-19 : 05:29:17

Hi,

Today while debugging an SP, i noticed unwanted spaces in the output.

Execute the below print statment and you can see some spaces in the output when casting to CHAR type.

PRINT 'BOHRA' + CAST(1 AS CHAR) + 'hAI'
PRINT 'BOHRA' + CAST(1 AS varchar) + 'hAI'

--output received
BOHRA1 hAI
BOHRA1hAI

By using the varchar, I could remove the unwanted spaces.
I just want to know why the space is coming in the first statement

Thanks,
Bohra

Kristen
Test

22859 Posts

Posted - 2010-03-19 : 05:32:03

CHAR is fixed length, and will pad with spaces. Varchar will not.

You have not specified any size for CHAR nor VARCHAR, so you will be getting the default size.

FWIW I always put a size on them, that way there is no misunderstanding between me, the SQL parser

, and my colleagues.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-19 : 05:33:05

It is becuase you are converting to CHAR without specifying the length which defaults to 30
So char datatype preserves the leading spaces whereas varchar doesn't

This is why you should always epecify the length while convertion
Refer this for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-19 : 05:33:39

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-19 : 05:34:09

You should always give the needed length when casting/converting!
It looks like standard length of casting to char is 2 and char is always going fill the column with trailing spaces to the end, hence there is a space in your output.
PRINT 'BOHRA' + CAST(1 AS CHAR(1)) + 'hAI' should work.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-19 : 05:35:25

This old rocker came rolling home...lalala

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-03-19 : 05:37:45

quote:
Originally posted by webfred

It looks like standard length of casting to char is 2

That's just the forum's HTML formatting doing that (because the [CODE] tag was not used).

Tip: If you press REPLY on the post you will see the original text the user had, including additional spaces (in this case) and (on other posts) nice formatting when they forget to use the [CODE] tag

)

Kristen
Test

22859 Posts

Posted - 2010-03-19 : 05:38:20

Yeah, and ... get your skates on chaps!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-19 : 05:41:54

Thanks you all..
Due to work pressure i missed the default length concept..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-19 : 05:43:57

<<
It looks like standard length of casting to char is 2
>>

It depends. See my blog post

Madhivanan

Failing to plan is Planning to fail

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-19 : 05:48:40

The standard length of char is 30. As pointed out by Kristen, I forgot to use the Code tag while posting the output.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-19 : 05:57:29

quote:
Originally posted by Kristen

Yeah, and ... get your skates on chaps!

No skates - I think I should use a walking frame (german word: Rollator)

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-03-19 : 06:00:17

"The standard length of char is 30. As pointed out by ~~Kristen~~ Madhi"

I suggest you go grab a strong cup of coffee

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-19 : 06:01:35

As pointed out by Kristen, I forgot to use the Code tag while posting the output.
dito

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-03-19 : 06:03:30

quote:
Originally posted by webfred
No skates - I think I should use a walking frame (german word: Rollator)

Zimmer-frame here ... I always assumed it was a German work, but maybe just a brand name (yeah, I checked, its a USA company - and now I think about it "Zimmer" is German for room!).

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-19 : 06:06:09

quote:
Originally posted by Kristen

"The standard length of char is 30. As pointed out by ~~Kristen~~ Madhi"

I suggest you go grab a strong cup of coffee

Only bear will help me out...

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-19 : 06:09:20

quote:
Originally posted by Kristen

"The standard length of char is 30. As pointed out by ~~Kristen~~ Madhi"

I suggest you go grab a strong cup of coffee

There are 2 sentence in one line

The standard length of char is 30.

Full stop ends the sentence.

Second sentence is :
As pointed out by Kristen, I forgot to use the Code tag while posting the output.

Again thanks you all for taking time to reply and for fun..
I had hectic day but wish you all good day..

Kristen
Test

22859 Posts

Posted - 2010-03-19 : 07:22:30

Hahaha ... I'm off to get some strong coffee

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources