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 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 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 Oracle databases, many useful functions or tools are provided to help database administrators process data of these character types in the fastest way. 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. If the user accidentally enters the product number, the user 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 will cause a lot of trouble for subsequent processing. Because the Where statement is used to find records, "DT001" (without spaces at last) and "DT001" (with a space at last) are different conditions. If you want to use this condition for data update and query, you will encounter problems. Therefore, when writing related Update or Select statements, 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 format of this function 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 as 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 fill the header of a field 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 design length is not enough, for example, only five characters are set. As enterprise product records increase, the encoding content needs to be expanded. If the product information belongs to the packaging material type, add a character B to the header of the original product number. If the product information belongs to the customer service, you need to 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, a single-line character function is provided in the Oracle database system to help database administrators and enterprises solve this problem. This function is the RPAD function. The command format is RPAD (C1, N, C2 ). The consciousness 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, the N characters on the Right of C1 are truncated. 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, you need to add a character F before the product number of the auxiliary information, and the total length after modification is changed to 6 bits. 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 (product number, 1) to extract the first character of the product number (here the product number is longer than 1, the one character on the right of the product, that is, the product category code ). This is also useful in report design and 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.

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 all product numbers originally in lowercase must be 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 can use a subquery or view help to update all product numbers that contain lower-case characters to uppercase. If the original data is in uppercase, the original data is kept unchanged (instead of the original data is written in uppercase, and the original data is changed in uppercase ). 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 LOWER-case characters, while LOWER-case characters 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. This is difficult to implement. It also provides a ready-made solution in the Oracle database, that is, using the Initchar function. This function is used to convert the first character of each word in a field to the size. 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 the display format of strings can be optimized through these three case-sensitive string control functions to make the 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.