Pad char(2) field with zero - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Pad char(2) field with zero

Author

Topic

JeffT
Posting Yak Master

111 Posts

Posted - 2006-04-06 : 13:01:37

Hi

I'm extracting a char(2) field from a table that has a value of '1' and writing it to another table but want the output field to be padded with a zero. I have tried this below:

right('00' + isnull(Field1, ' '),2

but the output field comes out as '1 ' (that's 1 followed by a space). Does anyone have any idea how I can do this or please point out what I'm doing wrong in my 'right(' fucntion above.
Thanks,
Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-06 : 13:12:23

right('00' + isnull(rtrim(Field1), ''),2)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-06 : 13:14:46

Your value of 1 is padded since you are using char. Check this out:


declare @s char(2)
set @s = '1'

if @s = '1 '
 print 'padded with a space'
else
 print 'unpadded'

Your code works fine if using varchar:


declare @s varchar(2)

set @s = '1'

select right('00' + isnull(@s, ' '),2)

[EDIT] Nigel shows the fix in his post for what I'm trying to show in my first example.

Tara Kizer
aka tduggan

JeffT
Posting Yak Master

111 Posts

Posted - 2006-04-06 : 13:16:11

Excellent ! That worked ! Thanks very much 'nr', I had been banging my head against the wall on that one for a while.
Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources