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