TRIM RTRIM LTRIM Performance

Question

I am only required to do RTRIM() in some part of query but if i do TRIM() will that affect performance.

Is Trim() Slower/Faster/Exactly same(NOT even has negligible difference) compared to RTRIM() AND LTRIM()?

This is with respect to Oracle 10g ONLY.

But in case of SQL Server 2005, Do we have function / method 'x()' such that it can replace RTRIM(LTRIM(' blah.. blah.. ')) to a single function ?

I simply mean of having "single" function for doing the same functionality what both RTRIM() AND LTRIM() does.

Answer 1

Based on this rough test there is a small difference:

DECLARE
n PLS_INTEGER := DBMS_UTILITY.get_time;
s1 VARCHAR2(32767);
s2 VARCHAR2(32767);
BEGIN
s1 := LPAD('X',15000,' ') || RPAD('X',15000,' ');
FOR i IN 1..1000000 LOOP
  NULL;
END LOOP;
DBMS_OUTPUT.put_line('Baseline: ' || (DBMS_UTILITY.get_time - n));
n := DBMS_UTILITY.get_time;
FOR i IN 1..1000000 LOOP
  s2 := LTRIM(s1);
END LOOP;
DBMS_OUTPUT.put_line('LTRIM: ' || (DBMS_UTILITY.get_time - n));
n := DBMS_UTILITY.get_time;
FOR i IN 1..1000000 LOOP
  s2 := RTRIM(s1);
END LOOP;
DBMS_OUTPUT.put_line('RTRIM: ' || (DBMS_UTILITY.get_time - n));
n := DBMS_UTILITY.get_time;
FOR i IN 1..1000000 LOOP
  s2 := TRIM(s1);
END LOOP;
DBMS_OUTPUT.put_line('TRIM: ' || (DBMS_UTILITY.get_time - n));
END;

The difference amounts to up to 0.000128 hundredth's of a second in the worst case:

Baseline: 0
LTRIM: 113
RTRIM: 103
TRIM: 8
Baseline: 0
LTRIM: 136
RTRIM: 133
TRIM: 8