Replace:
Replace funtion replaces a given string with another set of characters
Syntax:
replace( string1, string_to_replace, [ replacement_string ] )
Example:
SQL> select replace ('Hello World','World','Beutiful World') from dual;
REPLACE('HELLOWORLD'
--------------------
Hello Beutiful World
What will be the output, if we didnt provide the string to be replaced?
SQL> select replace ('Hello World','World') from dual;
REPLAC
------
Hello
The word World is removed.
What if, we need to replace "Hello" with "Hi" and "World" with "Beautiful World"?
Then we have to use TRANSLATE
TRANSLATE:
If we need to replace multiple set of characters with another given set of characters use TRANSLATE. Translate takes a list of characters to be replaced and a list of adjacent character which will replace the given one. It replaces by position, the first character of the given list is replaced with the first character of the given replacement list, second with second and so on.
As the above given example for replace, if there is no string in the equivalent position, then the character is dropped from the source text.
Examples:
Use translate to:
Replace a single character
SQL> select translate ('Hello World','W','i') from dual;
TRANSLATE('
-----------
Hello iorld
Replace a multiple characters
SQL> select translate ('Hello World','Wo','wO') from dual;
TRANSLATE('
-----------
HellO wOrld
Note: You have to make sure that at least one character should be given in the replacement list, else TRANSLATE wont give us expected result.
Use TRANSLATE to replace double quotes
SELECT TRANSLATE('"Darn double quotes "', 'A"', 'A')
FROM DUAL;
here if you hadn't given the character A, the value returned is null, ie everything is dropped.
SQL> SELECT TRANSLATE('"Darn double quotes "', '"', '')
2 FROM DUAL;
T
-
Use TRANSLATE to get the count of any items in the given string
SQL> WITH data AS (SELECT 'Whose line is it anyway' line FROM DUAL)
2 SELECT LENGTH(line)-LENGTH(TRANSLATE(line,'xaeiou','x')) Vowels
3 FROM data;
VOWELS
----------
8
SQL> WITH data AS (SELECT 'Whose line is it anyway' line FROM DUAL)
2 SELECT LENGTH(line)-LENGTH(TRANSLATE(line,'aeiou','')) Vowels
3 FROM data;
VOWELS
----------
Encryption and Decryption:
You can also use TRANSLATE for encryption and decryption
SQL> SELECT TRANSLATE('Market crashes down',
2 'abcdefghijklmnopqrstuvwxyz', '0123456789qwertyuiop[kjhbv')
3 FROM DUAL;
TRANSLATE('MARKETCR
-------------------
M0iq4p 2i0o74o 3tjr
Now let us use this encrypted value and try to decrypt
SQL> SELECT TRANSLATE('M0iq4p 2i0o74o 3tjr',
2 '0123456789qwertyuiop[kjhbv', 'abcdefghijklmnopqrstuvwxyz')
3 FROM DUAL;
TRANSLATE('M0IQ4P2I
-------------------
Market crashes down
Fine, works well...
Note: Translate always replace a single character at a time. Also it wont replace a single character to a multiple character as like we do with Replace function.
****
Hope this helps..Phoenix
