TRIM vs RTRIM function in ORACLE SQL

The main difference between TRIM and RTRIM is how they handle whitespace (or other specified characters) from strings.

1. TRIM:

Function: Removes both leading and trailing whitespace (or other characters) from a string.

Usage: You can use TRIM to remove characters from both ends of a string.

Syntax:

TRIM([characters] FROM string)

Example (with whitespace):

SELECT TRIM(' ABC ') AS trimmed_string;
-- Result: 'ABC'

Example (with specified characters):

SELECT TRIM('X' FROM 'XXXABCXXX') AS trimmed_string;
-- Result: 'ABC'

This removes all instances of the character X from both the beginning and the end of the string.

1. RTRIM:

Function: Removes only trailing whitespace (or other specified characters) from the right end of a string.

Usage: Use RTRIM when you want to remove only the spaces (or other characters) from the right side of a string, not the left.

Syntax:

RTRIM(string)

Example (with whitespace):

SELECT RTRIM('ABC ') AS trimmed_string;
-- Result: 'ABC'

Example (with specified characters):

SELECT RTRIM('ABCXXX', 'X') AS trimmed_string;
-- Result: 'ABC'

This removes the X characters only from the end.

Key Differences:

TRIM removes characters from both sides of the string (left and right).

RTRIM removes characters only from the right side of the string (trailing).

When to Use:

TRIM: When you need to clean up spaces or characters from both ends of the string.

RTRIM: When you need to clean up trailing characters, typically spaces or unwanted characters at the end of the string.