TRIM RTRIM LTRIM Performance

Question

I only need to do RTRIM() in some part of the request, but if I do TRIM (), it will affect performance.

Is Trim() Slower / Faster / Similarly (NO even a slight difference) compared to RTRIM() AND LTRIM() ?

This applies only to Oracle 10g.

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

I just mean the presence of a "single" function to perform the same function as for RTRIM() AND LTRIM() .

Answer 1

Based on this rough test, there is a slight 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; 

In the worst case, the difference is up to 0.000128 hundredth of a second:

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

Answer 2

The difference in performance, as a rule, is not detected, especially if it is in a query that receives its data from a table. Choose the one that suits your requirements.

Answer 3

The question does not matter for SQL Server, as it implements LTRIM and RTRIM , but not TRIM .

See the list of SQL 2005 String Functions .

Answer 4

I have never seen anything like this in Trims tests in SQL Server. I used it a lot and found little performance when working with a lot of data ... low performance.