Trimming a string in versions before 8i

Here is a utility function that combines the LTRIM and RTRIM for pre-8i shops.

In many data manipulation and querying scenarios, you may need to trim a string. To do this in Oracle, LTRIM and RTRIM are available. Here is a utility function that combines the two. Note: this function is a standard feature of Oracle 8i and higher. So it will be of use for those who use Oracle versions less than 8i.

CREATE OR REPLACE FUNCTION trim(p_string VARCHAR2)
RETURN VARCHAR2 IS
 v_new_string  VARCHAR2(1000);
BEGIN
 SELECT RTRIM(LTRIM(p_string))
 INTO   v_new_string
 FROM   DUAL;
 RETURN v_new_string;
END trim;

The result:

select trim('  ssss  ')
from dual;

TRIM
----
ssss

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in December 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

This Content Component encountered an error
Close