DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
chandra.shekhar@tcs.com



Group memberships:
Premium Members

Joined: 17 Jan 2011
Posts: 353
Location: Mumbai, India
Points: 2975

Post Posted: Tue Dec 12, 2017 5:12 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hi,

I have been asked to remove the spaces from all the columns of a table. So I simply added the TRIM function in the SELECT SQL which worked fine.

But my Client asked me to change it and use LTRIM(RTRIM)) function.
I said that they both will work fine as long as we are removing the spaces. But he's almost forcing me to use LTRIM RTRIM.

Does LTRIM(RTRIM)) works better than TRIM given the scenario of removing spaces only? Rolling Eyes

_________________
Thanx and Regards,
ETL User
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42283
Location: Denver, CO
Points: 217108

Post Posted: Tue Dec 12, 2017 7:31 am Reply with quote    Back to top    

So, this is a database question? If so, it would help to know which one. Wink

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow

Last edited by chulett on Tue Dec 12, 2017 8:53 am; edited 1 time in total
Rate this response:  
Not yet rated
chandra.shekhar@tcs.com



Group memberships:
Premium Members

Joined: 17 Jan 2011
Posts: 353
Location: Mumbai, India
Points: 2975

Post Posted: Tue Dec 12, 2017 8:00 am Reply with quote    Back to top    

Yes, you can say that.
These functions would be applied at the Teradata Database using the SQL.

_________________
Thanx and Regards,
ETL User
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42283
Location: Denver, CO
Points: 217108

Post Posted: Tue Dec 12, 2017 8:53 am Reply with quote    Back to top    

I'm sorry but why not simply read the documentation? TRIM defaults to BOTH left and right trim, a.k.a. leading and trailing as well as space as the character to trim and I'm assuming you let it default. Suggesting that you need to nest those two functions to accomplish what the original already does is a bit... silly. And makes LTRIM and RTRIM a bit redundant. IMHO.

ps. And I'm not aware where the combo would "work better" than TRIM in any situation. But perhaps I just need enlightenment after all these years. Wink

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42283
Location: Denver, CO
Points: 217108

Post Posted: Tue Dec 12, 2017 9:24 am Reply with quote    Back to top    

The only other thing that pops into my head is I have seen specialized implementations of TRIM that not only trim leading/trailing but also internal 'extra' spaces as well. If memory serves, the DataStage Server job trim function does that. But that's not SQL and not ANSI compliant, which Teradata's trim function is.

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
chandra.shekhar@tcs.com



Group memberships:
Premium Members

Joined: 17 Jan 2011
Posts: 353
Location: Mumbai, India
Points: 2975

Post Posted: Tue Dec 12, 2017 10:15 am Reply with quote    Back to top    

I couldn't agree with you more Chulett.
I know that it is better and efficient to use TRIM instead of nested RTRIM and LTRIM functions if need to remove the spaces from a column.

I just asked this because my client says that nested one would work better.
I just wanted to know who's right, I or my client ? Very Happy

Quote:
The only other thing that pops into my head is I have seen specialized implementations of TRIM that not only trim leading/trailing but also internal 'extra' spaces as well.

That is the TRIM function available in Datastage Transformer stage.

_________________
Thanx and Regards,
ETL User
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 274

Points: 2760

Post Posted: Tue Dec 12, 2017 11:26 am Reply with quote    Back to top    

Easy. Your client is right ! Do it their way, make them happy, get paid lol.

------------
I don't know the implementation under the hood but it could be argued either way if you can't get your hands on this.

consider -- all those need to do is check the extreme end of the data and remove whitespace. For trailing, that means assigning the byte after the last non whitespace byte to zero. For leading, it means adjusting the pointer to the data to point to the first non whitespace byte as starting point. In both cases, the work done is trivial. To combine the operations into 1 function.. there is no way I see to reduce the work, so all you save is the function call and function delay. But here is the kicker... in parallel, if you optimally called trailing before leading, you could do both at once, as by the time you started to adjust the leading pointer you already found the trailing point, so each cpu can doctor the same string without hurting the other.

so if it can optimally call the 2 functions in parallel, its identical.
if it can't, calling one function is a tiny handful of sub nanosecond delay per record. After a few billion, you have a couple of seconds wasted.
Rate this response:  
Not yet rated
PaulVL



Group memberships:
Premium Members

Joined: 17 Dec 2010
Posts: 1167

Points: 7709

Post Posted: Tue Dec 12, 2017 4:41 pm Reply with quote    Back to top    

Differ that question to a Teradata DBA.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours