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:

You can find that the results are displayed normally.

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

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.

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

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.

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

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

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

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

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

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

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

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!