Differences between translate and replace

Differences between translate and replace

Today, the nesting of replace and translate in the Oracle database is a bit confused, so I went online and looked at it, and it suddenly became clear:

The problems encountered today are as follows:

Replace (TRANSLATE (a. deal_msg, '2013', ''),'', '') AS deal_msg,

This is only part of an SQL statement. What does it mean? Is to set. in the deal_msg field, replace 0 and 1 with spaces. Of course, replace 01 with two spaces and replace. in deal_msg, 23456789 is deleted, whether consecutive or a single number, and then two consecutive spaces in the replaced string are replaced with one. Why? Let's take a look at the rules of translate and replace!

1. translate:

TRANSLATE (CHAR, FROM, TO), replace the from in char with the corresponding character in to. If the from length is greater than the to length, delete the characters that appear in char after the from length is greater than. A simple example:

Select translate ('anhw ', 'ang', 'A') from dual;

Query results:

---------

Ahw

Result Analysis: because A of the third parameter corresponds to a of the second parameter, a of the first parameter is replaced with A, and the length of the second parameter is 3, the length of the third parameter is 1, which is less than the length of the second parameter. Therefore, the n, g, and ng parameters in the first parameter are deleted. Because anhw has n, therefore, n is deleted, and the result is

Ahw.

Ii. replace

REPLACE (char, search_string, replacement_string), REPLACE all the strings search_string in char with the replacement_string. If the second parameter does not exist in the first parameter, the returned result is the original string of the first parameter. For example:

Select replace ('anhw ', 'ang', 'A') from dual;

Query results:

-----------

Anhw

Result Analysis: Since ang has no consecutive characters in anhw, It is not replaced and the original string is returned.

Select replace ('anhw ', 'A', 'A') from dual;

Query results:

-----------

Ahw

Result Analysis: because an has consecutive characters in the anhw string, it is replaced with A and the returned result is Ahw.