Oracle String Operations

 The simple TRIM function can trim blank spaces from the left and right sides of a string.

SELECT '[' || TRIM('  ABCDEF  ') || ']' FROM DUAL;

STR_TRIM
[ABCDEF]

To Trim specific characters at the leading or trailing end of the string you can do the same as follows.

LTRIM and RTRIM will trim leading and trailing characters mentioned in the second parameter.

SELECT '[' || LTRIM('**ABCDEF**','**') || ']' as trim FROM DUAL;

STR_LTRIM
[ABCDEF**]

SELECT '[' || RTRIM('**ABCDEF**','**') || ']' as trim FROM DUAL;

STR_RTRIM
[**ABCDEF]

To TRIM characters from both sides of the string you can use a combination of LTRIM and RTRIM as follows.

SELECT '[' || LTRIM(RTRIM('**ABCDEF**','**'),'**') || ']' as trim FROM DUAL;

STR_LTRIM_RTRIM
[ABCDEF]

Alternatively we can use TRIM with LEADING and TRAILING and BOTH as follows.

SELECT '[' || TRIM(LEADING '*' FROM '**ABCDEF**') || ']' as trim FROM DUAL;

STR_TRIM_LEAD
[ABCDEF**]

SELECT '[' || TRIM(TRAILING '*' FROM '**ABCDEF**') || ']' as trim FROM DUAL;

STR_TRIM_TRAIL
[**ABCDEF]

And to trim charactes from both sides of the string you can use BOTH.

SELECT '[' || TRIM(BOTH '*' FROM '**ABCDEF**') || ']' as trim FROM DUAL;

STR_TRIM_BOTH
[ABCDEF]

String Padding

If you want to pad the left and right of a string with special characters, you can do as below.

SELECT LPAD(RPAD('TESTSTRINGPADDING', LENGTH('TESTSTRINGPADDING') + 20,'*'), LENGTH('TESTSTRINGPADDING') + 20*2,'*')FROM DUAL;

STR_PADDING
********************TESTSTRINGPADDING********************

TRANSLATE

The translate function replaces every occurance of the character in the search string with the corresponding character in the replacement string. It is a single character replacement functions.

Search String                Replacement String

1                                    5

2                                    6

3

Here all occurrences of character '1' will be replace by character '5'

All occurrences of character '2' will be replace by character '6'

All occurrences of character '3' will be replace by a blank character since there is no replacement character for '3'.

SELECT TRANSLATE('12TEST34','123','567') AS translate FROM DUAL;

translate
56TEST74

Replaces all occurances of '1' with '5', '2' with '6' and '3' with '7'.

   

SELECT TRANSLATE('12TEST34','123','56') AS translate FROM DUAL;

translate
56TEST4

Replaces 12TEST34 with 56TEST4. Since no replacement character for '3', all occurances of '3' substituted with blank.

 

SELECT TRANSLATE('123TEST45613','123','56') AS translate FROM DUAL;

translate
56TEST4565

Replaces 123TEST45613 with 56TEST4565. Similar to the example above.

   

SELECT TRANSLATE('123TEST45613','123','5678') AS translate FROM DUAL;

translate
56TEST4565

In the above, there is no corresponding search character for '8' So it as not effect on the output string.

   

SELECT TRANSLATE('123TEST45613','1239','5678') AS translate FROM DUAL;

translate
567TEST45657

Similarly in the above, since there is no character '9' in the main string, it has no effect of the output string. 

The Oracle REPLACE function

SELECT REPLACE('12TEST34','TEST','REPLACEPATTERN') AS REPLACEPATTERN FROM DUAL;

REPLACEPATTERN
12REPLACEPATTERN34

For replace the pattern should be continuous else it wont replace a partial pattern on broken pattern. In below example it will return the same query string output as it does not find the replace pattern as a continuous string.

SELECT REPLACE('12TEST34','1234','REPLACEPATTERN') AS NOREPLACEPATTERN FROM DUAL;

NOREPLACEPATTERN
12TEST34

Below is an example of the oracle CONCAT functtion and hwo to concatenate multiple strings using the oracle CONCAT function.

SELECT CONCAT('ABC','DEF') AS CONCAT FROM DUAL;

CONCAT
ABCDEF

Example to concatenate multiple strings.

SELECT CONCAT(CONCAT('ABC','DEF'),'GHI') AS MULTICONCAT FROM DUAL;

MULTICONCAT
ABCDEFGHI

To TRIM the LAST n characters from a string using SUBSTR

SELECT SUBSTR('TESTSUBSTR', 1, LENGTH('TESTSUBSTR') - 3) TESTSUB FROM DUAL;

TESTSUB
TESTSUB

To TRIM the LAST n characters from a string using REGEXP_REPLACE

SELECT REGEXP_REPLACE('TESTSUBSTR', '...$') REGEXPREPLACE FROM DUAL;

REGEXPREPLACE
TESTSUB

Thought for the day

There is no substitute for the confidence that today - in this time and place - I am obeying the will of GOD.