This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes formatting a number Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "formatting a number" Watch "formatting a number" New topic
Author

formatting a number

Mary Wallace
Ranch Hand

Joined: Aug 25, 2003
Posts: 138
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

Joined: Oct 29, 2001
Posts: 8877
    
    8

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.


"blabbing like a narcissistic fool with a superiority complex" ~ N.A.
[How To Ask Questions On JavaRanch]
Mary Wallace
Ranch Hand

Joined: Aug 25, 2003
Posts: 138
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

Joined: Oct 29, 2001
Posts: 8877
    
    8

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30392
    
150

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 &.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: formatting a number