How to remove space-like characters in Oracle Database and table Structures

In the work involving database operations, we often encounter equivalent comparisons rather than fuzzy queries for two strings: for example, compare two names to be equal? However, when entering a string into the system, the operator may enter spaces or TAB keys (I put such a string that looks like a space, it may not actually be a space character. What should I do?

1. First, remove spaces for comparison (for example, remove all spaces in the name ):

Update table_name set xm = replace (xm ,'');

If you want to find which records contain '', you can use related statements.

Then you can make a comparison.

2. However, we may find that some characters with spaces in the middle or end of the names are not removed, but they are obviously not general spaces, so it is suspected that they are Tab Jian. Therefore, we can refer to the online solution to remove the TAB key and use chr (9) to replace the Space key (the ASCII value of the TAB key is 9 ). First, take a record with spaces of the class for the experiment:

SELECT replace (xm, chr (9) from table_name where bh = 'xxx ';

Using this method will remove some real Tab key spaces and then adopt the batch update method.

3. But I was not so lucky in the situation that I was not able to remove the Space key class. What should we do? I decided to get the ASCII code value of this space key first, and then use the chr (ASCII code value) method for processing.

①. Obtain the length () of the entire string containing the space-like key to determine the starting position of the space-like key in the string and the length of the space-like key.

②. Obtain the ASCII value of the space-like key: ascii (substr (xm, n, m); n is the starting position of the space-like key; m is the length.

③. SELECT replace (xm, chr (ASCII code value obtained in step 2) from table_name where bh = 'xxx ';

Solve the problem. However, I found that the ASCII code value obtained in step 2 is 41377, which is the ASCII code value of a Chinese character and looks like a space, so I suspect that, will it be the space key and TAB key in the Chinese state? No. I doubt, is it the difference between full-width and half-width? Because we generally input characters in the halfwidth. In accordance with step 1, "1. First, remove spaces for comparison ......" Try and enter a space in the full-width corner to search. The result is pleasantly surprised. All the class spaces that were not removed are the spaces entered in the full-width state, it is also confirmed that the entered space at the full angle has an ASCII code of 41377.

Finally, I found that I used a cow because the problem was very simple. However, using the 3rd. mentioned method to get the ASCII code value and then remove it is universal!