Oracle PL/SQL TRIM Function

Oracle PLSQL TRIM FunctionSQL TRIM Function is used to remove the specified characters from left and right sides of a string. Oracle TRIM Function returns the remaining string after removing the specified characters from left and right side of a string. PLSQL TRIM Function syntax with SQL TRIM String and SQL TRIM WHITESPACE examples are mentioned below.


Oracle TRIM Function Syntax

SELECT TRIM([trailing | leading | both [trim characters]] string_1)
FROM table_name;

In the above Oracle TRIM Function Syntax:-

  • string_1 is the string to be trimmed
  • ‘Leading’ will remove the trim_characters from start_position in string_1.
  • ‘Trailing’ will remove the trim_characters from the end of the string_1.
  • ‘Both’ will remove the trim_characters from beginning and end of string_1
  • If we omit ‘leading’, ‘trailing’ or ‘both’ from the TRIM function then the TRIM function trims the string_1 from beginning and end.
  • If no trim_charachter is mentioned then TRIM function removes the white spaces from string_1

Oracle TRIM Function – Use in SQL TRIM String Example

Oracle TRIM Function can be used with the SQL SELECT Statement to TRIM a string.

For example, the below SQL TRIM String query returns the string after stripping the white spaces from left and right sides.

SELECT TRIM(' Tech Honey ')"SQL TRIM String"
FROM dual;

The above SQL TRIM String example returns ‘Tech Honey’

Note: We have aliased TRIM(‘ Tech Honey ‘) as SQL TRIM String.


Oracle TRIM Function – SQL TRIM WHITESPACE Example

Oracle TRIM Function can be used in SQL TRIM WHITESPACE query.

For example, the SQL TRIM WHITESPACE query below returns the string after removing the whitespaces from left and right side.

SELECT TRIM(' ' from ' Tech Honey ') "SQL TRIM WHITESPACE"
FROM dual;

The above SQL TRIM WHITESPACE query returns ‘Tech Honey’

Note: We have aliased TRIM(‘ ‘ from ‘ Tech Honey ‘) as SQL TRIM WHITESPACE.


Oracle TRIM Function – Removing LEADING Characters Example

Oracle TRIM Function can be used to remove any leading character from a string.

For example, the below PLSQL TRIM Function query returns the string after removing all the leading 1s from the string.

SELECT TRIM(LEADING 1 FROM '1111Tech Honey11111') "PLSQL TRIM Function"
FROM dual;

The above PLSQL TRIM Function query returns ‘Tech Honey11111’

Note we have aliased TRIM(LEADING 1 FROM ‘1111Tech Honey11111’) as PLSQL TRIM Function.


Oracle TRIM Function – Removing TRAILING Characters Example

PLSQL TRIM Function can be used to remove any trailing character from a string.

For example, the below TRIM Function query returns the string after removing all the trailing 1s from the string.

SELECT TRIM(TRAILING 1 FROM '1111Tech Honey11111') "PLSQL TRIM Function"
FROM dual;

The above PLSQL TRIM Function query returns ‘1111Tech Honey’.

Note: We have aliased TRIM(TRAILING 1 FROM ‘1111Tech Honey11111’) as PLSQL TRIM Function.


Oracle TRIM Function – Removing LEADING and TRAILING Characters Example

Oracle TRIM Function can be used to remove both the LEADING and TRAILING characters from a string.

For example, the TRIM Function query returns the string after removing leading and trailing 1s from the string.

SELECT TRIM(BOTH 1 FROM '1111Tech Honey11111') "Oracle TRIM Function"
FROM dual;

The above TRIM Function query returns ‘Tech Honey’.

Note: We have aliased TRIM(BOTH 1 FROM ‘1111Tech Honey11111’) as Oracle TRIM Function.


Tagged , , , , , , , , , , , . Bookmark the permalink.