replace(convert(char()) loses spaces.

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 2000 Forums

Transact-SQL (2000)

replace(convert(char()) loses spaces.

Author

Topic

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-07 : 11:17:43

On v7 sp2 system
These (I think) should all produce the same result.
For some reason the one with convert(char(12)) inside the replace loses the spaces.
Anyone know why - and if the same thing happens on other systems.

ansi_paddings doesn't seem to make a difference.
Also happens on v2000 sp2

select replace('123456.12 ','.','.') + 'x'
123456.12 x

select replace(convert(varchar(12),'123456.12 '),'.','.') + 'x'
123456.12 x

*************
select replace(convert(char(12),'123456.12 '),'.','.') + 'x'
123456.12x
**************

select convert(varchar(12),'123456.12 ') + 'x'
123456.12 x

select convert(char(12),'123456.12 ') + 'x'
123456.12 x

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

Edited by - nr on 10/07/2002 11:21:38

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-07 : 13:12:42

No comments anyone?

Forgot this site doesn't display spaces too well.
The one with asterisks round it has lost all the spaces betwen the number and the 'x' - all others have left them there.

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

Edited by - nr on 10/07/2002 13:13:55

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-07 : 13:24:13

It seems REPLACE does an implicit conversion to varchar and/or trim, cause I got this to work properly:

SELECT convert(char(12),replace('123456.12 ','.','.') + 'x' )

ANSI_PADDING settings did not affect the original.

Edited by - robvolk on 10/07/2002 14:06:56

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-07 : 21:21:42

Yep that's what I've had to do.
actually
convert(char(20),replace(convert(char(20),amt),'.',','))
All because we need a comma for a decimal point instead of a full stop.

It's not a convert to varchar as that doesn't lose spaces (one of the examples above). It's doing a trim for some reason.

I was surprised that it's only convert char that it happens with and not convert varchar.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources