Solved

padding Zeros on a decimal and removing the decimal place

Posted on 2014-10-08
5
602 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now