Removing (,/ -) from the phone column
Moderators: chulett, rschirm, roy
Removing (,/ -) from the phone column
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
AlphaNumeric Phone Number
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
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
-
- Premium Member
- Posts: 483
- Joined: Thu Jun 12, 2003 4:47 pm
- Location: St. Louis, Missouri USA
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
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
-
- Premium Member
- Posts: 483
- Joined: Thu Jun 12, 2003 4:47 pm
- Location: St. Louis, Missouri USA
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
Tony
-
- Premium Member
- Posts: 483
- Joined: Thu Jun 12, 2003 4:47 pm
- Location: St. Louis, Missouri USA
OK, I just wrote a small routine to do what you wanted.
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
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)
Tony
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Performance tip.
will be faster than
and the difference is greater the longer the string in RetValue is.
Code: Select all
RetValue := TestChar
Code: Select all
RetValue = RetValue : TestChar
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Performance tip 3.
Preloading ValidChars at compile time will give a small gain in performance. Instead ofuse
Preloading ValidChars at compile time will give a small gain in performance. Instead of
Code: Select all
ValidChars = "ABC..."
Code: Select all
Equate ValidChars To "ABC..."
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.