Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What is the solution to containing numbers and special symbols in Oracle strings

2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

This article shows you the Oracle string contains numbers, special symbols of the solution is, the code is concise and easy to understand, absolutely can make your eyes bright, through the detailed introduction of this article hope you can get something.

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

If you encounter such problems, you can try it in accordance with the above methods.

The above is the solution to the problem that Oracle strings contain numbers and special symbols. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.

Views: 292

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report