Oracle strings contain numbers, special symbols for sorting

Problem Description:

A cell, need to follow the plot, Wendy, unit number, room number, but sort by address description, because the string contains a number, so the result is as follows,

Building 1th is supposed to be building 2nd, but the search results are building 10th.

Try to solve

Replace with regular expressions

Results:

Although the building number is normal, but will find the room number sort of chaos. Keep trying.

The ultimate approach:

Using the Translate function

It can be found that the results are displayed normally.

The following is attached translate use method

First, the syntax:
TRANSLATE (STRING,FROM_STR,TO_STR)
Second, the purpose
Returns a string that replaces each character in the FROM_STR (all occurrences) with the corresponding character in To_str. TRANSLATE is a superset of the functionality provided by REPLACE. If FROM_STR is longer than TO_STR, then extra characters in from_str that are not in TO_STR are removed from the string because they do not have the corresponding substitution characters. To_str cannot be empty. Oracle interprets an empty string as null, and if any of the arguments in translate are null, the result is null.
Third, allowed to use the location
Procedural statements and SQL statements.
Iv. examples
SQL code
1. SELECT TRANSLATE (' Abcdefghij ', ' abcdef ', ' 123456 ') from dual;
2. TRANSLATE (
3.--------------
4.123456ghij
5.
6. SELECT TRANSLATE (' Abcdefghij ', ' abcdefghij ', ' 123456 ') from dual;
7. transl
8.----------
9.123456
Syntax: TRANSLATE (expr,from,to)
Expr: Represents a string of characters, from and to is a relationship that corresponds from left to right, and is considered null if it cannot correspond.
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 @, D corresponds to a null value, will be removed)
Therefore: The results are: @ #c ##@ @def and @ #c ##@ @ef
Syntax: TRANSLATE (expr,from,to)
Expr: Represents a string of characters, from and to is a relationship that corresponds from left to right, and is considered null if it cannot correspond.
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 @, D corresponds to a null value, will be removed)
Therefore: The results are: @ #c ##@ @def and @ #c ##@ @ef

Examples are as follows:

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

SELECT TRANSLATE (' 2krw229 ', ' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ', ' 9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX ') "License" from DUAL

Example two: Keep the number and remove the other uppercase letters.

Select TRANSLATE (' 2krw229 ', ' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ', ' 0123456789 ') ' TRANSLATE example ' from DUAL

Examples of Rayong additions are as follows:

Example three: the example proves to be handled by character, not by Byte, and if to_string has more characters than from_string, the number of characters that appear is useless and does not throw an exception.

Select TRANSLATE (' I am Chinese, I love China ', ' Chinese ', ' China ') ' TRANSLATE example '

From DUAL

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

Select TRANSLATE (' I am Chinese, I love country ', ' China ', ' Chinese ') ' TRANSLATE example '

From DUAL

Example five: The following example proves that if the second argument is an empty string, the entire return is null.

SELECT TRANSLATE (' 2krw229 ',
' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
")" License "
From DUAL

Example six: In the bank transfer often see the account person only display the last word of the name, the rest with asterisks instead, I will use translate to do something similar.

SELECT TRANSLATE (' Chinese ',

substr (' Chinese ', 1,length (' Chinese ')-1),

Rpad (' * ', Length (' Chinese '), ' * ')) "License"

From DUAL

　　

Oracle strings contain numbers, special symbols for sorting