Interception of special characters in SQL statements

These two days in the project encountered a problem, the data stored in the database and the user would like to display some inconsistent data, but the data deposited is correct. In this case, we need to do some processing when displaying the data. The problem is handled in the project by using a data dictionary to format the data when it is displayed. My problem is because the workflow is increased, so the data dictionary does not work and can only be processed in advance. The final choice is to process the data when the SQL statement is used.

First look at the data format stored in the database, company name + "*" + subkey name, as follows:

The data information that the user needs to display is just the name of the child behind, so we only need to intercept the "*" and the previous characters. You can use the following function to intercept special characters in sql:

SUBSTR (Subitem,instr (Subitem, ' * ', 1) +1)

Among them, the SUBSTR function and the InStr function are used.

1, substr (String,start_position,[length]) substring, return string

The first parameter represents the string to intercept, the second represents the starting position, and the third represents the length of the interception, which can be omitted.

2, InStr (string,substring,position,ocurrence) Find the location of the string

The first parameter represents the source string, the second is the character to look for, the third is the starting position to look for, and the fourth is the number of characters to find.

This way, after we have processed the data in the background, the data displayed in the foreground is as follows:

Summary:

There are other ways to handle the data, choose the right way, or get a temporary approach and then refine it. Through this study, the application of SQL statements has added a new understanding. Of course, there are still many places to learn and progress, and continue to fighting.

Interception of special characters in SQL statements