Solved

padding Zeros on a decimal and removing the decimal place

Posted on 2014-10-08
5
642 Views
Last Modified: 2014-10-08
hi there,
I have a column with decimal places and I need to pad it with zeros but I also need to remove the decimal place or the dot in this case but keep the number. this is what I need

Original money column value 10.13
padded final result 00000001013

if I execute the following statement
SELECT RIGHT('000000000000'+CAST('10.13' AS VARCHAR(12)),12)

I get almost what I need but I need to get rid off the period (.) or dot that signifies the decimal. I don't need to round it or truncate it I just need to display as a whole.

That's the first question, second I need to build a dynamic way to build my sql query for instance create a store procedure and the parameter makes up the Select portion

something like this:
CREATE PROCEDURE usp_test (
@columnName Varchar(20))
as
SELECT @columnName, Column2
FROM MyTable

@columnName will be check prior to be submitted to ensure the column exist

Thanks,
cohfl
0
Comment
Question by:COHFL
5 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 40368690
DECLARE @value      MONEY;

SELECT @value = 10.13

SELECT RIGHT('000000000000'+REPLACE(CAST(@value AS VARCHAR), '.', ''),12)

For the second part you will need to use dynamic-sql but be wary of sql injection.

http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40368695
SELECT replace(RIGHT('000000000000'+CAST('10.13' AS VARCHAR(12)),12), '.', '')

Open in new window

0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 40368809
declare @money as decimal(4,2) = '10.13'

select right(stuff(replace(@money,'.',''),1, 0,'0000000000'),11)
0
 

Author Comment

by:COHFL
ID: 40369142
oh im sorry one thing I forgot to mention, in the event that one number is negative I need the sign at the end
for instance if the number on my original example was -10.13 the final number should be like this:
00000001013-

or if the number is positive  (10.13) then the output should be
00000001013+
0
 

Author Closing Comment

by:COHFL
ID: 40369158
Thanks! your solution gave me what I need it. For my last question I just add a case statement when the number is lower than 0 then add a - and the else a +.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question