?
Solved

padding Zeros on a decimal and removing the decimal place

Posted on 2014-10-08
5
Medium Priority
?
706 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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. …
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

764 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