Shulou(Shulou.com)06/01 Report--

Problem description:

A residential area needs to be sorted by district, building, unit number and room number, but when sorted by address description, the string contains numbers, resulting in the following result

Building 1 should be followed by Building 2, but the query result is Building 10.

Try to solve the problem

Replace with regular expressions

Results:

Although the order of the building numbers is normal, you will find that there is confusion in the sorting of room numbers. Continue to think of a way

The ultimate solution:

Use the translate function

It can be found that the results are displayed normally.

The usage of translate is attached below.

First, grammar:

TRANSLATE (string,from_str,to_str)

II. Purpose

Returns the string after replacing each character in the (all occurrences) from_str with the corresponding character in the to_str. TRANSLATE is a superset of the functionality provided by REPLACE. If from_str is longer than to_str, extra characters in from_str rather than to_str will be removed from string because they do not have corresponding replacement characters. To_str cannot be empty. Oracle interprets the empty string as NULL, and if any parameter in TRANSLATE is NULL, the result is also NULL.

III. Allowable locations

Procedural statements and SQL statements.

IV. Examples

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 have an one-to-one correspondence from left to right. If they do not correspond, they will be regarded as null.

For 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 the value corresponding to d will be null and will be removed)

Therefore: the results are @ # c##@@def and @ # c##@@ef in turn

Syntax: TRANSLATE (expr,from,to)

Expr: represents a string of characters. From and to have an one-to-one correspondence from left to right. If they do not correspond, they will be regarded as null.

For 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 the value corresponding to d will be null and will be removed)

Therefore: the results are @ # c##@@def and @ # c##@@ef in turn

Examples are as follows:

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

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

Example 2: keep the numbers and remove other uppercase letters.

SELECT TRANSLATE (0123456789 ABCDEFGHIJKLMNOPQRSTUVWXYZ) Translate example FROM DUAL

Additional examples of Luo Yong are as follows:

Example 3: the example proves that it is processed according to characters, not bytes. If the number of characters in to_string is more than that in from_string, the extra characters seem to be of no use and will not throw an exception.

SELECT TRANSLATE ('I am Chinese, I love China', 'China', 'China') "Translate example" FROM DUAL

Example 4: the following example proves that if the number of characters in from_string is greater than to_string, then the extra characters will be removed, that is, the three characters of ina will be removed from the char parameter, of course, case-sensitive.

SELECT TRANSLATE ('I am Chinese, I love China', 'China',' China') "Translate example" FROM DUAL

Example 5: the following example proves that if the second parameter is an empty string, the entire null is returned.

SELECT TRANSLATE (0123456789 ABCDEFGHIJKLMNOPQSTUVWXYZ) License FROM DUAL

Example 6: when transferring money to a bank, I often see that the account holder only shows the last word of his name, and the rest is replaced by an asterisk. I will use translate to do something similar.

SELECT TRANSLATE ('Chinese', substr ('Chinese', 1 License length ('Chinese')-1), rpad ('*, length ('Chinese'),'*')) "Chinese" FROM DUAL

Summary

The above is introduced by the editor to solve the sorting problem of numbers and special symbols in the Oracle string. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.