Converting decimal to specific char format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rick - 30 Dec 2008 22:54 GMT

In a stored procedure, I have a decimal-defined variable as DEC(13,2).
I need to write its value to a column in a table I'll be creating. This
column needs to display its value as 11 chars, with the first char being
a space and values after that padded with beginning zeroes:

2850.00     should display as ' 0002850.00'
999126.88      should display as ' 0999126.88'
0.00        should display as ' 0000000.00'

Any ideas on how to do this? I'm trying to decide on how to define the
column, and how to convert the DEC(13,2) variable when I'm inserting it.
Thanks for any help.

Reply to this Message

Erland Sommarskog - 30 Dec 2008 23:27 GMT

> In a stored procedure, I have a decimal-defined variable as DEC(13,2).
> I need to write its value to a column in a table I'll be creating. This
[quoted text clipped - 8 lines]
> column, and how to convert the DEC(13,2) variable when I'm inserting it.
> Thanks for any help.

' ' + replace(str(@decval, 10, 2), ' ', '0')

Beware that if the value exceeds 9 999 999, the value will be replaced
by stars.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

--CELKO-- - 31 Dec 2008 00:28 GMT

Display is done in the front end in a properly designed tiered
architecture, not in the database. Is this nameless data element used
for computations or is it a string that happens to have digits? A
data element should be represetrned in one and only one format in the
schema.

Reply to this Message