• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

formatting a number

 
Mary Wallace
Ranch Hand
Posts: 138
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In the db i have a number with eight or nine digits. I need to show the user like this 12-3456789 if its eight digits 01-23455678. Is there any way to do in select statement?
or after taking frm db should i format it?
any help appreciated.
 
Joe Ess
Bartender
Posts: 9297
10
Linux Mac OS X Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can use a SQL Function like SUBSTRING to format the number on the query then parse the incoming string into a numeric type to do inserts/updates. I prefer to store "numbers" like this (i.e. phone number, SSN) as CHAR or VARCHAR and use business rules to enforce the format.
 
Mary Wallace
Ranch Hand
Posts: 138
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
but sometimes its eight digits then i have to add a leading zero to it.
If its nine digits its fine i can write like this
SUBSTR(accnum,1,2)||'-'||substr(fax,3,7). But the que is how to format if its eight digits I mean like this 01-1234567

 
Joe Ess
Bartender
Posts: 9297
10
Linux Mac OS X Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's why I went with a CHAR or VARCHAR. If it has leading zeros and dashes embedded in it, the data isn't numeric. If you don't want to alter your database, you will be stuck checking the length of the string and appending a zero to the short ones.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34669
366
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mary,
It seems easier to do in Java when you get the result back. Also, keep in mind that if you use substring and concatenation in the select clause, your query will be database dependent. For example, Oracle uses || for concatenation and Access uses &.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic