Toad World® Forums

How too format phone numbers?


#1

I have query that selects phone numbers which were entered using space, brackets, hypens and periods.

What is the best way to have the number returned stripped to the numieric chars only?


#2

What type of database are you connected to? And where does the result set end up being used? IE: In a Toad Data Report or Pivot Grid, etc.

Debbie


#3

Hi Debbie
I am connected to Oracle Database 11g. I am trying to use Toad Date Analysts to compare fields with different formats. The final results will automated the query to export a CVS file when when similarities found.


#4

You could use the TRANSLATE function. It will find and replace a list of characters with what you want so you can do a compare.

See reference http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions196.htm#i1501659

Here is a sample of comparing two phone fields. I think this is what you are talking about. Correct?

SELECT CONTACT.CONTACT_ID,
CONTACT.LAST_NAME,
TRANSLATE (CONTACT.HOME_PHONE, ’ ()./- ', ‘___’) AS Trans_Home_Phone,
TRANSLATE (CONTACT.BUSINESS_PHONE, ’ ()./- ', ‘___’)
AS Trans_Business_Phone
FROM QUEST_STAGE.CONTACT CONTACT
where TRANSLATE (CONTACT.HOME_PHONE, ’ ()./- ', ‘___’) <> TRANSLATE (CONTACT.BUSINESS_PHONE, ’ ()./- ', ‘___’)

BTW, I am not a SQL specialist either. What I did is opened up the Code Snippets from the View Menu. I looked through them for a function that did what I wanted. This one is under Oracle | Single-Row Functions | Character Functions Returning Character Values. You can double click on the snippet to add it as template in the Query/Editor page of Query Builder. At the bottom is a link to the help reference regarding the function.

I actually added the translated columns as Calcualted fields in the Query Builder but you could freehand them also.

See if this gives you what you need.

Debbie


#5

Debbie

Thanks you, It worked great. I used TDA Functions Translate and Concat

PO_VENDOR_SITES_ALL.AREA_CODE,
PO_VENDOR_SITES_ALL.PHONE,
TRANSLATE (CONCAT (PO_VENDOR_SITES_ALL.AREA_CODE, PO_VENDOR_SITES_ALL.PHONE),’ -’,’ ’ ) AS “Vendor Phone number”,

Really really appreciated your help


#6

Glad to hear this.

My service is part of the product:)