Solve the Oracle string Sorting Problem that contains numbers and special symbols.

Solve the Oracle string Sorting Problem that contains numbers and special symbols.

Problem description:

A residential area needs to be sorted by the residential area, building, unit number, and room number. However, when sorted by the address description, the following results are generated because the string contains numbers,

After building 1, it should be building 2, but the query result is building 10.

Try to solve

Replace with regular expression

Result:

Although the building number sorting is normal, the room number sorting is messy. Continue to find a solution

Ultimate solution:

Use the translate function

The result is displayed normally.

The following describes how to use translate.

I. Syntax:

TRANSLATE(string,from_str,to_str)

Ii. Purpose

Replace each character in from_str with a string after the corresponding character in to_str. TRANSLATE Is a superset of the functions provided by REPLACE. If from_str is longer than to_str, extra characters in from_str instead of to_str will be deleted from the string because they do not have replacement characters. To_str cannot be blank. Oracle interprets the NULL String as NULL, and if any parameter in the TRANSLATE Is NULL, the result is also NULL.

3. Permitted locations

Procedural and SQL statements.

Iv. Example

SQL code

SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual;  TRANSLATE (  --------------  123456ghij  SELECT TRANSLATE('abcdefghij','abcdefghij','123456') FROM dual;  TRANSL  ----------  123456 

Syntax: TRANSLATE(expr,from,to)

Expr: represents a string of characters. from and to correspond one to one from left to right. If not, it is considered as a null value.

Example:

Select translate ('abcbbaadef ', 'ba',' # @ ') from dual (B will be replaced by #, a will be replaced by @) select translate ('abcbbaadef ', 'bad', '# @') from dual (B will be replaced by #, a will be replaced by @, and d will be replaced by a null value)

Therefore, the results are as follows: @ # c ###@ def and @ # c ##@ ef.

Syntax:TRANSLATE(expr,from,to)

Expr: represents a string of characters. from and to correspond one to one from left to right. If not, it is considered as a null value.

Example:

Select translate ('abcbbaadef ', 'ba',' # @ ') from dual (B will be replaced by #, a will be replaced by @) select translate ('abcbbaadef ', 'bad', '# @') from dual (B will be replaced by #, a will be replaced by @, and d will be replaced by a null value)

Therefore, the results are as follows: @ # c ###@ def and @ # c ##@ ef.

Example:

Example 1: convert a number to 9, convert other uppercase letters to X, and then return.

SELECT TRANSLATE('2KRW229','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX')                       "License"FROM DUAL

Example 2: retain the number and remove other uppercase letters.

SELECT TRANSLATE('2KRW229','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789') "Translate example"FROM DUAL

Luo Yong's supplementary example is as follows:

Example 3: The example shows that it is processed by character rather than byte. If to_string has more characters than from_string, the extra characters seem useless, and does not cause exceptions.

Select translate ('I am Chinese, I Love China', 'China') "Translate example" FROM DUAL

Example 4: The following example shows that if the from_string Character Count is greater than to_string, the extra characters will be removed, that is, the ina three characters will be removed from the char parameter, of course it is case sensitive.

Select translate ('I am Chinese, I love China', 'China', 'China') "Translate example" FROM DUAL

Example 5: The following example shows that if the second parameter is a null string, the entire return value is null.

SELECT TRANSLATE('2KRW229','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','') "License"FROM DUAL

Example 6: When transferring funds at a bank, you often see that the account owner only displays the last word of the name, and the rest are replaced by asterisks. I will use translate to make something similar.

Select translate ('China', substr ('China', 1, length ('China')-1), rpad ('*', length ('China '), '*') "License" FROM DUAL

Summary

The preceding section describes how to sort numbers and special symbols in Oracle strings. I hope it will help you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!