Solved

padding Zeros on a decimal and removing the decimal place

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

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

759 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

20 Experts available now in Live!

Get 1:1 Help Now