Oracle/PLSQL: Replace Function

Oracle/PLSQL: Replace Function

--------------------------------------------------------------------------------

In Oracle/PLSQL, the replace function replaces a sequence of characters in a string with another set of characters.

The syntax for the replace function is:

replace( string1, string_to_replace, [ replacement_string ] )

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the replace function simply removes all occurrences of string_to_replace, and returns the resulting string.



Applies To:

•Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g


For example:

replace('123123tech', '123'); would return 'tech'
replace('123tech123', '123'); would return 'tech'
replace('222tech', '2', '3'); would return '333tech'
replace('0000123', '0'); would return '123'
replace('0000123', '0', ' '); would return '    123'

www.techonthenet.com/oracle/functions/rtrim.php

Oracle/PLSQL: Rtrim Function

--------------------------------------------------------------------------------

In Oracle/PLSQL, the rtrim function removes all specified characters from the right-hand side of a string.

The syntax for the rtrim function is:

rtrim( string1, [ trim_string ] )

string1 is the string to trim the characters from the right-hand side.

trim_string is the string that will be removed from the right-hand side of string1. If this parameter is omitted, the rtrim function will remove all trailing spaces from string1.



Applies To:

•Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g


For example:

rtrim('tech   '); would return 'tech'
rtrim('tech   ', ' '); would return 'tech'
rtrim('123000', '0'); would return '123'
rtrim('Tech123123', '123'); would return 'Tech'
rtrim('123Tech123', '123'); would return '123Tech'
rtrim('Techxyxzyyy', 'xyz'); would return 'Tech'
rtrim('Tech6372', '0123456789'); would return 'Tech'



The rtrim function may appear to remove patterns, but this is not the case as demonstrated in the following example.

rtrim('Techxyxzyyy', 'xyz'); would return 'Tech'

It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.



The rtrim function can also be used to remove all trailing numbers as demonstrated in the next example.

rtrim('Tech6372', '0123456789'); would return 'Tech'

In this example, every number combination from 0 to 9 has been listed in the trim_string parameter. By doing this, it does not matter the order that the numbers appear in string1, all trailing numbers will be removed by the rtrim function.


Oracle/PLSQL: Substr Function

--------------------------------------------------------------------------------

In Oracle/PLSQL, the substr functions allows you to extract a substring from a string.

The syntax for the substr function is:

substr( string, start_position, [ length ] )

string is the source string.

start_position is the position for extraction. The first position in the string is always 1.

length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.



Note:

If start_position is 0, then substr treats start_position as 1 (ie: the first position in the string).

If start_position is a positive number, then substr starts from the beginning of the string.

If start_position is a negative number, then substr starts from the end of the string and counts backwards.

If length is a negative number, then substr will return a NULL value.



Applies To:

•Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g


For example:

substr('This is a test', 6, 2) would return 'is'
substr('This is a test', 6) would return 'is a test'
substr('TechOnTheNet', 1, 4) would return 'Tech'
substr('TechOnTheNet', -3, 3) would return 'Net'
substr('TechOnTheNet', -6, 3) would return 'The'
substr('TechOnTheNet', -8, 2) would return 'On'