Removing (,/ -) from the phone column

[rkumar28]

Hi,

I have situation where I have a phone column in teradata that is loaded from a flat file. The flat file data contains different format for phone numbers something like this: (123)456-4567, 123/456/4567, (123) 456 / 4567, (123) 456 - 4567, 123, 456 - 4567.

Is there a function or a way to transform this number to this format: 1234564567 i.e tripping off all the brackets and slashes and other characters.

Thanks for help

[chulett]

Use the Transform called DIGITS, it uses 'Oconv' and 'MTC' (I believe) to do exactly that.

[lclapp]

Ans = Oconv(Arg1,"MCN") You can create a function or use inline....leslie

[chulett]

Arg, you're right - "MCN". No need to create one, as I said it already exists - check under "Built-In\String" for the DIGITS Transform.

[lclapp]

Close only counts in horseshoes, hand granades or nuclear warfare :D Would there be any overhead savings using inline vs the function DIGITS?...leslie

[ray.wurlod]

No.
When the server job is compiled, the definition of the Transform becomes inline code.
This can be verified by inspecting the code generated from the Transformer stage that uses the Transform, which is in the RT_BPnnn directory in your project (where nnn is the job number from DS_JOBS).

[chulett]

Well... seeing as how it is a Tranform, the end result is the same as if it was 'inlined', so no - no overhead savings. Just easier to understand.

[rkumar28]

Thanks for all the time and advice in this regards. It did worked. I am able to get only the numbers now.

But today I came accross one more scenario like:
(800) 123 - ABC2, 800-ABC-EFG1.
It is alpha numeric. I need to keep the characters and numbers and not the brackets or dashes. Can you please help me show how to eliminate only the brackets and dashes or any other character(like /, commas) from an alpahanumeric phone number and just keep the numbers.

Thanks again for your time in advance.
Raj

[tonystark622]

Raj,

You can use the Convert() function to remove the desired characters by converting them to "". e.g. Convert("()- ", "", "(800) 123-ABC2") (note the space at the end of the first argument).

Tony

[ketfos]

Tony,
This works well, provided you have well defined characters which can be part of the input string.
How do you take care of different possible charcters which are in the input but you do not want in the output string?
I mean, if I know my input string will only have have numbers, apha, #,$,% then I can code using the COnvert function?
What what happens if the input string has other character other than that mentioned above?
Ketfos

[tonystark622]

No argument that if you don't have a well defined set of characters to remove that Convert() won't work. As far as I know there is no BASIC function that would do this. I suppose you could write a routine to parse your string and eliminate all but a set of characters that you have defined as valid for the string.

Tony

[tonystark622]

OK, I just wrote a small routine to do what you wanted.

Code: Select all

FUNCTION StripChars(Arg1)
  ValidChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789#$%"

  RetValue   = ""

  For i = 1 to Len(Arg1)
    TestChar = Arg1[i,1]
    If Count(ValidChars, TestChar) > 0 Then
      RetValue = RetValue : TestChar
    End
  Next i

Ans = RetValue

RETURN(Ans)

You just have to define the set of valid characters. You may have a problem with NULL input values, so be sure you modify this code to handle it.

Tony

[ray.wurlod]

Performance tip.

Code: Select all

RetValue := TestChar

will be faster than

Code: Select all

RetValue = RetValue : TestChar

and the difference is greater the longer the string in RetValue is.

[ray.wurlod]

Performance tip 2.

The Index() function will be faster than Count(), because it can stop at the first occurrence. Count will need to process the entire string on each iteration of the loop.

Code: Select all

If Index(ValidChars, TestChar, 1) Then
   RetValue := TestChar
End

[ray.wurlod]

Performance tip 3.

Preloading ValidChars at compile time will give a small gain in performance. Instead of

Code: Select all

ValidChars = "ABC..." 

use

Code: Select all

Equate ValidChars To "ABC..."