How can I insert a money value as a padded string in another table?
example $1.25 gets inserted to another table as 00000125
I want 8 total characters and no decimal
another example would be 4,225.99 becomes 00422599
can this be done?
thank you!!declare @.m money
set @.m = $1.25
select @.m, RIGHT(REPLICATE('0',8) +
convert(varchar(8),convert(int,@.m*100)),8)
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 10:15:08 -0800, paulmac106@.gmail.com wrote:
Quote:
Originally Posted by
>Ok my last formatting question.
>
>How can I insert a money value as a padded string in another table?
>
>example $1.25 gets inserted to another table as 00000125
>
>I want 8 total characters and no decimal
>
>another example would be 4,225.99 becomes 00422599
>
>can this be done?
>
>thank you!!
Quote:
Originally Posted by
declare @.m money
set @.m = $1.25
>
select @.m, RIGHT(REPLICATE('0',8) +
convert(varchar(8),convert(int,@.m*100)),8)
>
Roy Harvey
Beacon Falls, CT
>
On 22 Feb 2007 10:15:08 -0800, paulmac...@.gmail.com wrote:
>
Quote:
Originally Posted by
Ok my last formatting question.
>
Quote:
Originally Posted by
How can I insert a money value as a padded string in another table?
>
Quote:
Originally Posted by
example $1.25 gets inserted to another table as 00000125
>
Quote:
Originally Posted by
I want 8 total characters and no decimal
>
Quote:
Originally Posted by
another example would be 4,225.99 becomes 00422599
>
Quote:
Originally Posted by
can this be done?
>
Quote:
Originally Posted by
thank you!!
I had a similar project. I did this and it works great:
REPLACE(REPLACE(CONVERT(char(8), @.m), '.', ''), ' ', '0')
-Utah|||thanks that worked great.
any idea why this doesn't work:
REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)),' ','0')
it just won't seem to put the zero in...very strange
i get this for 15: '15 '|||(paulmac106@.gmail.com) writes:
Quote:
Originally Posted by
thanks that worked great.
>
any idea why this doesn't work:
>
REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)),' ','0')
>
it just won't seem to put the zero in...very strange
>
i get this for 15: '15 '
Good question. Seems like the reailing spaces are stripped when the
string is passed to replace(). Probably, because there is a conversion
to varchar, but trailing spaces should be retained, as long as the
setting ANSI_PADDING is in effect.
It looks like a bug to me.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||i got it to work using:
RIGHT('0000'+REPLACE(SUM(tblLines.fldUnits), ' ', '0'), 4)
results in 0015 where SUM(tblLines.fldUnits)=15|||Erland Sommarskog (esquel@.sommarskog.se) writes:
Quote:
Originally Posted by
(paulmac106@.gmail.com) writes:
Quote:
Originally Posted by
>thanks that worked great.
>>
>any idea why this doesn't work:
>>
>REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)),' ','0')
>>
>it just won't seem to put the zero in...very strange
>>
>i get this for 15: '15 '
>
Good question. Seems like the reailing spaces are stripped when the
string is passed to replace(). Probably, because there is a conversion
to varchar, but trailing spaces should be retained, as long as the
setting ANSI_PADDING is in effect.
>
It looks like a bug to me.
For what it's worth, I submitted
https://connect.microsoft.com/SQLSe...edbackID=259840
But since it works this way in SQL 2000, I would not really expect
any fix. It could break existing code.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
I've submitted a workaround to this Feedback item. If you
concatenate the empty string '' to the CHAR value before
REPLACE is applied, the hidden conversion to VARCHAR
retains the trailing blanks.
declare @.t char(6)
set @.t = 'A'
select
replace(@.t,space(1),'*'),
replace(@.t+'',space(1),'*')
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
-- C70DF007-1034-489C-A71E-108FBC89D553
Erland Sommarskog wrote:
Quote:
Originally Posted by
Erland Sommarskog (esquel@.sommarskog.se) writes:
>
Quote:
Originally Posted by
>(paulmac106@.gmail.com) writes:
>>
Quote:
Originally Posted by
>>>thanks that worked great.
>>>
>>>any idea why this doesn't work:
>>>
>>>REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)),' ','0')
>>>
>>>it just won't seem to put the zero in...very strange
>>>
>>>i get this for 15: '15 '
>>
>>Good question. Seems like the reailing spaces are stripped when the
>>string is passed to replace(). Probably, because there is a conversion
>>to varchar, but trailing spaces should be retained, as long as the
>>setting ANSI_PADDING is in effect.
>>
>>It looks like a bug to me.
>
>
For what it's worth, I submitted
https://connect.microsoft.com/SQLSe...edbackID=259840
>
But since it works this way in SQL 2000, I would not really expect
any fix. It could break existing code.
>
>
