Introduction to PL/SQL TRIM
PL/ SQL trim is the function provided in PL/ SQL Database Management System which helps to remove all the trailing or leading blank spaces or any other character if required. This function is mostly used to make sure that the data being stored in the column field is as much optimized as possible and does not contain any other blank spaces at starting or ending of the string being stored. However, this function can also be used in other scenarios where we have a requirement to remove the initial or ending occurrence of a particular character in the string. In this article, we will study the general syntax, usage, and implementation of PL/ SQL trim function along with the help of certain examples.
Syntax and Working
The syntax of the PL/ SQL trim function is as shown below:
TRIM ([BOTH | TRAILING | LEADING] character to be trimmed FROM) source string)
In the above syntax, the terms that are used and the working of trim function when they are specified or not specified is as explained below:
- TRAILING – Whatever source string is supplied in the input, if we want to remove all the occurrences of the character to be removed parameter from the source string that occurs in the ending portion of the source string by specifying the keyword TRAILING after TRIM () function inside it as the first parameter. If the characters at the end match with the character to be trimmed then it is removed from the end of the source input string.
- LEADING– Whatever source string is supplied in the input, if we want to remove all the occurrences of the character to be removed parameter from the source string that occur in the beginning portion of the source string by specifying the keyword LEADING after TRIM () function inside it as the first parameter. If the characters at the beginning match with the character to be trimmed then it is removed from the start of the source string.
- BOTH – When we specify the keyword BOTH in the first parameter of the TRIM syntax then all the matching character occurrences that have equal value as that of the character to be trim parameter are removed from the beginning and the ending of the source string. By default, if we don’t specify any value in the first parameter then it is considered as BOTH.
- Source string – The source string is the input string that s supplied from which all the trimming needs to be done.
Character to be trimmed – This character is considered for doing a match by oracle database to select which characters need to be removed. In case, if this parameter is not specified then the default value considered is blank space and the same value is considered as the character to be trimmed.
Output:
The string format is returned as the output of the input string. Whatever supplied string is processed for removal of the occurrences of the character to be trimmed is treated as the output.
Points to be considered:
In case if only one parameter is supplied in the trim () function then the DBMS removed all the trailing and leading occurrences of the blank spaces in the supplied parameter of the trim () function as this parameter is considered as the source string. If we try to specify either the source string ass null or the character to be trimmed as null the output of the trim function is null itself.
Support for Oracle/ PLSQL versions for TRIM() function:
The following versions of ORACLE support the usage of the TRIM() function:
- Oracle 8i
- Oracle 9i
- Oracle 10g
- Oracle 11g
- Oracle 12c
Advantages of PL/SQL TRIM
The most promising feature and advantage of the TRIM() function is that the parameters source input string and character to be trimmed are supported in all the datatypes which include VARCHAR2, NVARACHAR2, NCHAR, CHAR, NCLOB, or CLOB.
Examples of PL/SQL TRIM
Let us consider some examples which will help us to understand the implementation of the TRIM () function in PL/ SQL.
Example #1
Let us first consider an example that will help us to demonstrate how we can remove the LEADING occurrences of the particular character in the source string. Consider one string “33EDUCBA33” from which we want to remove the 3 characters from the beginning of the string. In such a case, we can make the use of following query program in PL/ SQL –
DECLARE
sampleInput string(20) := '33EDUCBA33';
BEGIN
dbms_output.put_line(TRIM(LEADING '3' FROM sampleInput));
END;
The output of the execution of the above program is as shown below:
Example #2
Now, let us consider the same input string and we will use the TRIALING parameter as the first parameter of the TRIM () function. This will lead to the removal of all the occurrences of the ending characters from the source string. The program for doing so in PL/ SQL will be as shown below:
DECLARE
sampleInput string(20) := '33EDUCBA33';
BEGIN
dbms_output.put_line(TRIM(TRAILING '3' FROM sampleInput));
END;
The output of the execution of the above program is as shown below:
Example #3
Let us consider an example where we will be using BOTH as the first parameter to the trim () function. In this case the program will become as shown:
DECLARE
sampleInput string(20) := '33EDUCBA33';
BEGIN
dbms_output.put_line(TRIM(BOTH '3' FROM sampleInput));
END;
The output of the execution of the above program is as shown below:
In the above program even if we don’t specify any parameter in first place like TRAILING, LEADING, or BOTH still the function will remove the beginning and ending occurrences of the character to be trimmed from the source string.
Conclusion
The trim () function is used in PL/ SQL to remove any of the occurrences of the particular character in either the beginning or the ending of the source string or even in both. This function is mostly used for efficient use of space in database while storing the strings which removes all the occurrences of blank spaces if present at the end of the string. Any character at end of the beginning can be removed when used properly.
Recommended Articles
This is a guide to PL/SQL TRIM. Here we discuss the Introduction, syntax, How to uses and working with trim functions, examples with code implementation. You may also have a look at the following articles to learn more –
