Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Numeric field add leading zeros and remove decimal

Posted on 2005-04-21
13
Medium Priority
?
808 Views
Last Modified: 2012-08-13
I have a numberic field (numeric(19,5).  I need to convert the numeric data so that I get back a max of ten digits: e.g 123.45, 51423.99, etc... returns 0000012345 or 0005142399.  when any number is not 10 digits, add leading zeros to make it 10 digits.
Any suggestions?
0
Comment
Question by:Pioneermfg
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 13833402
Hi Pioneermfg,

Right('0000000000' + Cast ((Cast MyField As Integer) As Varchar(10),10)

Should do it.

Tim Cottee
0
 
LVL 11

Expert Comment

by:lluthien
ID: 13833466
that one cuts of the decimal part due to the integer cast Tim
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 300 total points
ID: 13833493
Tim is on the right track but you'll lose your decimal portion of the field value.    try...

Right('0000000000' + Replace(Cast(myField as varchar), '.', ''), 10)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Expert Comment

by:lluthien
ID: 13833497
right (10000000000 + replace (aantaleenheden, '.', '' ) , 10)

will do the trick though
0
 
LVL 11

Expert Comment

by:lluthien
ID: 13833507
what he said :)
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 13833526
lluthien,

Wasn't reading it quite right!

Tim
0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 13833789
I failed to mention that the precision is 5 so an amount of 123.45 reads in the table as 123.45000.  I also need to remove the trailing zeros to my end result is: 0000012345.
0
 
LVL 11

Expert Comment

by:lluthien
ID: 13834072
right,

that's getting quite expensive to do in sql in my opinion
it's doable with multiple replace actions tho

are you sure you want to do this in your query?
maybe it is a better idea to play with the format on the receiving end?


0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 13835321
This all part of sending my payment records electronically to the bank.  This isn't my idea, but the banks.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13835350
Do you want to round or truncate the value to the penny before sending it?
0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 13835481
Yes I need to truncate the value to the penny.
0
 
LVL 11

Assisted Solution

by:lluthien
lluthien earned 200 total points
ID: 13835490
ehm..
strange though

how is the receiving end going to see the difference between
123.45
0.12345?
and 12345.00

after you finished this conversion?

seems kind of strange that these numbers all have the same result..
0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 13835765
I changed the precision from numeric(19.5) to numeric(19,2) this resolves the trailing zeros.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
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.
Suggested Courses

564 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