Solving the sorting problem of Oracle strings containing numbers and special symbols

This article will share with you how to solve the sorting problem that contains numbers and special symbols in Oracle strings. Recently, I encountered such a demand in a project, which required that a certain community be sorted according to community, building, unit number, and room number. It seems to be a very simple requirement that can be solved with a SQL statement. In fact, the routine is very deep. Below, I will share with you the sorting problem of numbers and special symbols in Oracle strings. I hope it can help you.

Problem description:

In a certain community, it is necessary to sort by community, building, unit number, and room number, but it is sorted by address description. At this time, because the string contains numbers, the following results are produced. Building No. 1 should be followed by Building No. 2, but the query result is Building No. 10.

Try to solve

Use regular expression to replace

## Result:

Although the building number sorting is normal, you will find that the room number sorting is confusing. Continue to think of ways

The ultimate solution:

Use the translate function

You can find that the results are displayed normally.

Attached below is how to use translate

1. Grammar:

TRANSLATE (string,from_str,to_str)

2. Purpose

Return to replace each character in (all occurrences) from_str with the corresponding character in to_str The string after the characters. TRANSLATE is a superset of the functionality provided by REPLACE. If from_str is longer than to_str, the extra characters in from_str but not in to_str will be removed from string because they have no corresponding replacement characters. to_str cannot be empty. Oracle interprets empty strings as NULL, and if any parameter in TRANSLATE is NULL, the result is NULL.

3. Allowed locations

Procedural statements and SQL statements.

4. Example

Sql code

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

Syntax: TR

ANSLATE( expr,from,to)

expr: represents a string of characters, from and to have a one-to-one correspondence from left to right. If they cannot correspond, they are regarded as null values.

Example:

select translate('abcbbaadef','ba','#@') from dual　（b将被＃替代，a将被＠替代）
select translate('abcbbaadef','bad','#@') from dual　（b将被＃替代，a将被＠替代，d对应的值是空值，将被移走）

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

Grammar:

TRANSLATE(expr,from,to)

expr: represents a string of characters, from and to have a one-to-one correspondence from left to right, If it cannot correspond, it is regarded as a null value.

Example:

select translate('abcbbaadef','ba','#@') from dual　（b将被＃替代，a将被＠替代）
select translate('abcbbaadef','bad','#@') from dual　（b将被＃替代，a将被＠替代，d对应的值是空值，将被移走）

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

Examples are as follows:

Example 1: Convert numbers to 9, other uppercase letters to X, and then return.

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

Example 2: Keep the numbers and remove other capital letters.

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

Luo Yong added the following examples:

Example 3: The example proves that it is processed according to characters, not bytes. If to_string If there are more characters than from_string, the extra characters seem to be of little use and will not cause an exception.

SELECT TRANSLATE('我是中国人,我爱中国', '中国', 'China') "Translate example"
FROM DUAL

Example 4: The following example proves that if the number of characters from_string is greater than to_string, the extra characters will be removed, that is, 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', '中国') "Translate example"
FROM DUAL

Example 5: The following example proves that if the second parameter is an empty string, null will be returned as a whole.

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

Example 6: When making bank transfers, I often see that only the last letter of the name of the account holder is displayed, and the rest are replaced with asterisks. I use translate. Make something similar.

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

If you encounter this kind of problem, you can try the above method.

Related recommendations:

About the usage summary of Contains function in Oracle

Detailed explanation of the basic principles of Oracle paging query

Oracle program development tips

The above is the detailed content of Solving the sorting problem of Oracle strings containing numbers and special symbols. For more information, please follow other related articles on the PHP Chinese website!