Removing (,/ -) from the phone column

tonystark622 · Post by **tonystark622** » Fri May 21, 2004 6:13 am

Thanks, Ray.

You're right, Index() would be faster. I just did a quick look in the Help file and missed it. Agreed with the Equate instead of the variable assignment, too.

With a function like this you want it to be as fast as possible.

Tony

tonystark622 · Post by **tonystark622** » Fri May 21, 2004 6:24 am

OK, here's the final version.

Code: Select all

FUNCTION StripChars(Arg1)
  Equate ValidChars to "1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz#$%"

  RetValue   = ""

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

  Ans = RetValue

RETURN(Ans)

I also moved the numbers to the front of the ValidChars string because they should occur more frequently in the data than alpha or other chars.

Tony

rkumar28 · Post by **rkumar28** » Fri May 21, 2004 8:01 am

Thank you all very much on providing the information. I really appreciate the help and advice and taking out time to help me resolving this issue. Thanks for the routine, it realy made my life little easy.

Thanks
Raj

tomengers · Post by **tomengers** » Fri May 28, 2004 12:46 pm

Leslie ...

That's Horseshoes, Handgrenades and Heavy Breathing.

... tom

mandyli · Post by **mandyli** » Mon May 31, 2004 12:32 am

Hi

I hope this very simpale. in the datastage Trim function is there.
Trim(ur string,",", A) that means remove ", " from ur string or ur fields..

please check it will work now..

rds
mandyli

ray.wurlod · Post by **ray.wurlod** » Mon May 31, 2004 1:07 am

Not unless you provide an expression or a quoted string as the third argument, for example Trim(string, ",", "A").
However, the Trim function can only remove occurrences of one character, to remove six would require six applications of the Trim function. It is not possible to provide a general solution using the Trim function.
Do become familiar with the other functions. Open up the on-line help from Designer, go to the topic "BASIC Tasks" and from there examine the data conversion and formatting topics, which will introduce you to the most commonly used functions in these categories.
It's always best to choose the right tool for any job; for this job Trim simply isn't the right tool.

(DataStage training can be had in Singapore - find out where by visiting the Ascential web site.)

nhyatt · Post by **nhyatt** » Fri Jun 04, 2004 5:10 am

I did have a requirement for returning alphanumeric before and the method I used was

Convert(Oconv(Oconv(FieldIn,"MC/A"),"MC/N"),"",FieldIn)

If you did need to keep some non alphanumeric characters you could use

Convert(Convert("#$%","",Oconv(Oconv(FieldIn,"MC/A"),"MC/N")),"",FieldIn)

not sure how this compares with the datastage routine speedwise though.

Regards
Nick

tonystark622 · Post by **tonystark622** » Fri Jun 04, 2004 6:27 am

I haven't waded all the way through it to understand it, Nick, but I tested it and it looks like it does the same thing that my routine does. Cool!

I don't know about speed, but I like it's brevity.

Tony

ray.wurlod · Post by **ray.wurlod** » Fri Jun 04, 2004 5:50 pm

Oconv (and Iconv) can handle a multi-valued list of conversion specifications, which are applied one after another.
So, even more brief:

Code: Select all

Convert(Convert("#$%","",Oconv(FieldIn,"MC/A":@VM:"MC/N"),"",FieldIn)

This is a good way to generate an SDK timestamp (one with no delimiters).

Code: Select all

Oconv(TheDate, "DYMD[4,2,2]":@VM:"MCN") : " 00:00:00"

tonystark622 · Post by **tonystark622** » Fri Jun 04, 2004 8:22 pm

Thanks, Ray. I appreciate the tip. I'm sure it also speeds this one up a tiny bit.

Take care,
Tony

DSXchange

Removing (,/ -) from the phone column

Removing (,/ -) from the phone column

Removing ,/-)