Summary of Character Processing Techniques in Oracle databases

In database development and maintenance, the data types most frequently accessed by the database administrator are character types, including string and date string. In the Oracle database, to help the database
Administrators can process data of these character types in the fastest way and provide many useful functions or tools. Today, I will talk about my experience and experiences in handling strings in Oracle databases.

　　1. Remove trailing space characters.

Sometimes, you need to remove the spaces at the end of the string during query or other processing. If the application software design is unreasonable, the space characters are stored in the database. For example
When the product number is used, the user accidentally enters "dt001" into "dt001"
". If the application can automatically Remove trailing spaces during design and save them to the database, nothing will happen. However, many applications are not designed in this way during development. This is after
Continued processing brings a lot of trouble. Because when the where statement is used to find records, "dt001" (with no space at last) and "dt001"
"(With a space at the end) Two are different conditions. If you want to use this condition for data update and query, you will encounter problems. Therefore, when you write the related update or select statement
You may need to remove the space symbol. Therefore, there are many solutions in Oracle databases. For example, the database administrator can use the ltrim function. The function format is
Ltrim [C1, C2]. The function is to remove any C2 characters on the left of C1. It ends when a string of different types is not C2, and then returns the remaining string. If you set the C2 string
If it is set to a space character (by default, it is a space character), you can remove the space symbol after dt001.

　　2. Automatically fill the header.

Sometimes it is possible that some fields are not large enough to be considerate during database design. When upgrading the database, you need to adjust the relevant fields. In this case, you may need
To meet the data consistency requirements. If you have an ERP system, you need a table of product information, which contains a product number field. At the beginning, when designing the product code
The length is not enough, for example, only five digits are set. As enterprise product records increase, the encoding content needs to be expanded. If the product information belongs to the packaging material type, you must add
Character B. If the product information belongs to the customer supply product, you must add a character C to the header of the original product number. If the product information belongs to the auxiliary material, add a character F before the original product number.
How can we achieve this demand? Do you want to modify them one by one? The manual modification workload is large and error-prone. This method is not feasible. In fact, the Oracle database system provides
A single-line character function can help database administrators and enterprises solve this problem. This function is the rpad function. The command format is rpad (C1, N, C2 ). Awareness of this function
Is to fill the character C2 on the right side of C1 until the total length of the string is N. By default, the value of C2 is a space. You can set this value as needed. If the length of C1 is greater than N
Take n characters on the Right of C1. To meet the preceding requirements, you only need to set the rpad function (product number, 6, F. Because the original product number is 5 bits, now we need to provide auxiliary information
Add a character F before the product number, and the total length after modification is changed to 6 characters. However, when using this function, you often need to use the where Condition Statement for restrictions.

This function can also be used to intercept strings. If the product number is still used, the first digit indicates the product type. In this case, the database administrator can use rpad
(1) to intercept the first character of the product number. (if the length of the product number is greater than 1, the first character on the right of the product is truncated, that is, the product category identifier code ). This is also useful in report design.
This helps simplify the display of reports.

　　3. Control the case sensitivity of characters.

When making reports, we may need to control the real format of strings, such as the case-sensitive display format. In addition, the previous income may be case-insensitive during database transplantation.
Database administrators need to adopt certain rules to standardize the character string writing specifications after transplantation. To simplify the implementation of these requirements, some tools are also provided in the Oracle database to solve these problems.
Question.

For example, the product number may be case insensitive when you enter the product number. The front-end applications do not have such control. Now the user wants all product numbers to be in uppercase and needs
All product numbers in lower case are converted to uppercase. If the number of products is large, manual modification will increase the workload. If you write a program to implement it, it is too complicated. In fact, we can
Use the string processing functions provided by the Oracle database for implementation. In a single-line character function set, a upper function is used to rewrite all strings to uppercase and return results. For this reason, we
You can use a subquery or view to update the product numbers containing lower-case characters in all product numbers to uppercase. If the original data is in uppercase, the original data is kept unchanged (instead of the original data in uppercase ).
The original lowercase letters are changed to uppercase letters ). Similarly, since the lower case can be changed to the upper case, the upper case can be changed to the lower case. By using the single-line string function lower, you can change all strings to lower-case output.
(Replace uppercase letters with lowercase letters, while lowercase letters remain unchanged ).

In addition, the case-sensitive control principle is complicated. For example, for strings with English addresses or English names, the first letter is required, while other characters are in lower case. There is a certain degree of implementation.
Difficulty. It also provides a ready-made solution in the Oracle database, that is, using the initchar function. This function converts the first character of each word in a field
Small. Other characters are lowercase strings. Generally, words in this field can be expressed by spaces, control characters, or punctuation marks. It can be seen that using these three case-sensitive string control functions, you can
Optimize the display format of the string to make it display more standard. Combined with the update statement, you can also update the data in batches in the database so that the data stored in the database meets the standardization requirements.

4. Complex replacement of strings.

A few years later, the city of Hangzhou began offering administrative divisions, for example, the city of Xiasha, which is directly under the jurisdiction of Hangzhou. To this end, a database administrator of an enterprise asked me if I could keep some records
The Xiasha city is changed to Xiasha area. What does it mean? That is to say, the database may contain the following records: "Xiasha city XX Steel Factory", "Hangzhou XX clothing factory", and "Binjiang district XX Information Co., Ltd .".
. Now the database administrator wants to change the content in Xiasha city to Xiasha area. This involves the replacement of some complex fields, which is difficult to implement. A database is a combination of structured data.
Word documents, you can use the find replacement function to solve this problem. So how can we implement it in Oracle databases? The search in Word documents is simulated in the Oracle database.
Replacement function.

For example, if a single-line character function has a function called replace, it can help the database administrator to achieve this requirement. The function format is
Replace (C1, C2, C3 ). The meaning is that after death, the C2 that appears in C1 is replaced with C3, and then the string is returned. What does this mean? In fact, it is the query in the Word document.
The replacement function is similar. Search for the content of C2 from the string C1. If found, use C3 instead. If C3
If the content is blank, all characters that contain C2 content in the field will be deleted. To meet the above requirements, you can set the parameter of this function to replace (adress, Xiasha city, bottom
Sha area ). Then, use the upadate statement to assign the value returned by this function to the address field. In this way, in the database address field, all the places in Xiasha city will be updated to Xiasha district. However
Changes will not affect other records. For example, we will not change Hangzhou City to Hangzhou district.

To do this, you can use the replace function to implement complex character replacement jobs. Sometimes, the replace function can be combined with other functions to achieve more
Complex content. For example, in the basic product information, the product number may contain the product category information. For example, the product number psd001, the first character P indicates a batch, and the second three characters SD indicates manual work.
. The automatic tool is pzd001. When a report is generated, if these characters are used to display the results, the report is obviously not readable. In this case, the database administrator can use words first.
String truncation function or use the header filling function mentioned above to intercept related strings, and then use the current search to replace the function, then, ZD and other characters can be converted into "automatic tools" and other representatives.
Meaning string, which improves the readability of the report. It can be seen that the replace function can be combined with other functions (the C1, C2, and C3 parameters can all be implemented using functions) to implement complicated replacement.
Function Change.