RTRIM(LTRIM()) help

[him121]

hi..
guys..
we have more than 100 columns in Source DRS Stage...
in derivation we want to write RTRIM(LTRIM()) for all the CHAR Columns..
this is taking lot of time to write manually..

is there any shortcut method to this.

thanx in advance

[chulett]

Not really. Your RTRIM/LTRIM syntax is database syntax, you could replace it with just Trim() in the derivation and accomplish the same thing.

If you are good with something like Perl or Awk (or perhaps a good editor), you may be able to export your job to .dsx or .xml format and make the substitutions that way. Then just import it back into the project and recompile.

[kcbland]

The 7.1 Designer has a macro (right click on a column) to propagate a common function, like TRIM, to all derivations in a link. Can save a lot of time, check it out.

[chulett]

That's true - forgot about that one. Open up your transformer and right-click in the derivation. You'll find something called Derivation Substitution that should get you what you need.

Available in 7.0 and up.

[ray.wurlod]

The correct function names are TRIMB and TRIMF rather than RTRIM and LTRIM.

But you can also use the extended form of TRIM, where the third argument of "B" removes leading and trailing. For example, TRIM(TheString, " ", "B") removes leading and trailing spaces.

One function is cheaper to execute than two. When you're processing milions of rows, every little gain helps.