Solve the problem of sorting numbers and special symbols in Oracle strings

Time:2019-6-12

Problem Description:

A residential area needs to be sorted according to the residential area, building, unit number and room number, but when sorted according to address description, because the string contains numbers, the results are as follows.

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

Trying to solve it

Replacement with regular expressions

Result:

Although the ranking of building numbers is normal, it will be found that there is confusion in the ranking of room numbers. Continue to think about ways

Ultimately:

Using translate function

It can be found that the results are displayed normally.

Attached below are translat usage methods

I. Grammar:

TRANSLATE(string,from_str,to_str)

Purpose

Returns the string after replacing each character in (all occurrences) from_str with the corresponding character in to_str. TRANSLATE is a superset of functions provided by REPLACE. If from_str is longer than to_str, the extra characters in from_str instead of to_str will be deleted 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. Permissible Location

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 

Grammar: TRANSLATE(expr,from,to)

Expr: Represents a string of characters. From and to correspond one-to-one from left to right. If they cannot correspond, they are regarded as null values.

Give an example:

Select translate ('abcbbaadef','b a','# @') from dual (b will be replaced by and a will be replaced by)
Select translate ('abcbbaadef','b a d','# @') from dual (b will be replaced by', a will be replaced by', D will be empty, will be removed)

Therefore, the results are as follows: @ c_ C def and @ c c @ ef

Grammar:TRANSLATE(expr,from,to)

Expr: Represents a string of characters. From and to correspond one-to-one from left to right. If they cannot correspond, they are regarded as null values.

Give an example:

Select translate ('abcbbaadef','b a','# @') from dual (b will be replaced by and a will be replaced by)
Select translate ('abcbbaadef','b a d','# @') from dual (b will be replaced by', a will be replaced by', D will be empty, will be removed)

Therefore, the results are as follows: @ c_ C def and @ c c @ ef

Examples are as follows:

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


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

Example 2: Keep the number and remove the 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 handled according to characters, not bytes. If the number of characters to_string is more than from_string, the extra characters seem to be of little use and will not cause an exception.

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

Example 4: The following example demonstrates that if the number of characters from_string is greater than to_string, the extra characters will be removed, that is, InAs will be removed from the char parameter, and of course case-sensitive.

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

Example 5: The following example demonstrates that if the second parameter is an empty string, the whole is returned null.


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

Example 6: When transferring money to a bank, it is often seen that the account holder only shows the last word of his name. The rest is replaced by asterisks. I’ll use translate to do something similar.

SELECT TRANSLATE ('Chinese',
Substr ('Chinese', 1, length ('Chinese') - 1),
Rpad ('*', length ('Chinese'),'*') License
  FROM DUAL

summary

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

Recommended Today

The method of obtaining the resolution of display by pyqt5

The code is as follows import sys from PyQt5.QtWidgets import QApplication, QWidget class Example(QWidget): def __init__(self): super().__init__() self.initUI() #Interface drawing to initui method def initUI(self): self.desktop = QApplication.desktop() #Get display resolution size self.screenRect = self.desktop.screenGeometry() self.height = self.screenRect.height() self.width = self.screenRect.width() print(self.height) print(self.width) #Show window self.show() if __name__ == ‘__main__’: #Create applications and objects app […]