Difference in translate and replace

[n.ashis]

If i try the following query it works fine in replacing the spaces between two words i.e.

SELECT REPLACE('Hi All',' ','')
FROM DUAL;

O/P: HiAll

But if i try the same query with translate function i am getting no o/p i.e.

SELECT TRANSLATE('Hi All',' ','')
FROM DUAL;

O/P: No o/p

Again if i try the following query i.e.

SELECT TRANSLATE('Hi All','H ','H')
FROM DUAL;

O/P: HiAll

I am able to get the desired o/p. So please help me understand how the operation differ in above three cases.

Thanks in advance...

[debasisdas]

Translate replaces by position, the first character of the list to match is replaced by the first character of the replacement list. The second character with the second, and if there are characters in the list to match that do not have positional equivalents in the replacements list they are dropped.

Replace replaces the string to match with the replacement string. The replacement of a single character is the same as that of TRANSLATE.

[debasisdas]

These two Oracle function TRANSLATE and REPLACE are mainly used to remove special characters from a string.

TRANSLATE replaces individual characters, and REPLACE is meant to search and replace words

please check the sample code for reference.
----------------------------------------------

select translate('beer bucket','beer','milk') as translate,
          replace  ('beer bucket','beer','milk') as replace_1,
          replace  ('beer bucket','beer')        as replace_2
    from   dual;

SELECT TRANSLATE('CAG-TTT-GAC-ACA-TGG-ATC', ' ACGT', 'GATC') DNA
FROM dual;

Encryption / Decryption
-------------------------

SELECT TRANSLATE('this is a secret',
'abcdefghijklmnopqrstuvxyz', '0123456789qwertyuiop[kjhbv')
FROM dual;

SELECT TRANSLATE('p78o 8o 0 o42i4p',
'0123456789qwertyuiop[kjhbv', 'abcdefghijklmnopqrstuvxyz')
FROM dual;

[n.ashis]

Thanks Debasis for your reply, like your reply made things more clear to me, still i have a doubt regarding TRANSLATE function i.e. if it is possible to remove a special character with TRANSLATE function then why it fails in case of a blank space.

For e.g.

SELECT TRANSLATE('@sia','@','A')
FROM DUAL;

O/P: Asia (WORKS FINE)

But

SELECT TRANSLATE(' Asia',' ','')
FROM DUAL;

O/P: No o/p (FAILS)

Is it that its not possible to translate the blank space alone to no character(i.e. simply removing it) ???

[n.ashis]

I hope i got the answer now i.e. since there is nothing listed in the replacement list or string, so there will be nothing that would get replaced with the blank space ...