0

I have a bunch of dollar amounts in a varchar field which should be 8 characters long every time, to the format of 00000.00

For example, I have

3495
12.7
-.38
-324.81

which needs to be

03495.00
00012.70
-0000.38
-0324.81

Can this be done in a single update?

lefeal
  • 53
  • 5
  • 6
    what RDBMS are you using? – Gary_W Dec 23 '16 at 15:04
  • 5
    If the amounts are stored as numbers, then the answer is "no", regardless of the database. If the amounts are stored as strings, then the question is why you are storing numbers in an inappropriate data type. The correct question is how to format the data on output; not how to do an update. – Gordon Linoff Dec 23 '16 at 15:07
  • What is the datatype of the column in which these values are stored? It would have to be some form of character data (I guess `CHAR(8)` would make sense) for the database to actually store the leading and trailing 0's; and since this makes the data non-numeric that may cause you other problems depending what you're doing. It may be better to let the values be as they are in the table and use your front-end to format them for display. – Mark Adelsberger Dec 23 '16 at 15:07
  • 2
    You cannot "pad" a numeric data value in the database unless it is stored as character (text) data, and if you are doing that you lose the ability to perform any arithmetic operations on it. That would be stup.... uh, not optimal. Formatting should be done on data in the client, or during data fetching and delivery, when it is converted to text for display purposes. – Charles Bretana Dec 23 '16 at 15:08
  • It is a varchar field. – lefeal Dec 23 '16 at 15:28
  • Which DBMS are you using? Postgres? Oracle? And why on earth are you storing numbers in a varchar column. That is almost always a really bad idea –  Dec 23 '16 at 15:31
  • 1
    @lefeal "It is a varchar field". But you're storing numbers in it? That, as others have also pointed out, is a very bad design. You can pad it with zeros etc in your user interface (or other output). If you store the data in the database in an appropriate numeric format, you don't have to care about it when you store it. And you can do arithmetic operations on the data properly. And there's no ambiguity about what number the field represents, either, which could happen with text representations. – ADyson Dec 23 '16 at 15:34
  • I'm surprised no one commented that the sign counts towards the 8. – xQbert Dec 23 '16 at 16:01

0 Answers0