Solved

padding Zeros on a decimal and removing the decimal place

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

809 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