Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1406
  • Last Modified:

Phone Number Format with extension in DB2

Hi,

   We are on DB2 UDB 9.7.
  I want to write a SQL to format the phone number and extension to display the following format. My current format is : Phone Number: 5555555555 and extension in the format: 1234.
Please help.
(555) 555-5555 Ext. 1234

Open in new window

0
pvsbandi
Asked:
pvsbandi
  • 2
  • 2
1 Solution
 
tliottaCommented:
...to format the phone number and extension...

You showed us the presentation format that you want to see, but you didn't tell us anything about the data type(s?) and size(s?) of the column(s?) in your database. What is being formatted? What is the column definition? Is it numeric or character? How big?

Tom
0
 
pvsbandiAuthor Commented:
Sorry..here is the information.

   Two columns in my database: STAFF.Work_PHONE_TX is a varchar(10).
                                                   STAFF.Work_Phone_EXTN_TX is a varchar(5).

If STAFF.Work_PHONE_TX = '5555555555' and STAFF.Work_Phone_EXTN_TX = '1234', then i want the format to be displayed as (555) 555-5555 Ext. 1234
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
This ought to do it:

select '(' ||
       substr(Work_PHONE_TX,1,3) ||
       ') ' ||
       substr(Work_PHONE_TX,4,3) ||
       '-' ||
       substr(Work_PHONE_TX,7,4) ||
       ' Ext. ' ||
       Work_Phone_EXTN_TX as formattedPhone                  
  from deleteme

Open in new window


HTH,
DaveSlash
0
 
pvsbandiAuthor Commented:
Thank you! works as expected.
0
 
tliottaCommented:
There is one minor potential problem. The STAFF.Work_PHONE_TX column is VARCHAR, so the column expects values that might have fewer than 10 characters. If it should be expected always to have 10 significant characters, the presentation possibly should ensure that the characters are always right-justified in a CHAR(10) intermediate field.

Tom
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now