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;
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.