What is the solution to the problem of spaces in CHAR type data in Oracle?

Is there any way to convert the empty space in CHAR type data in Oracle

You can use a script to convert all CHAR type fields into VARCHAR2 type, and cut off the spaces in the string, as follows:

declare

begin

for c in (select *

from user_tab_columns t1

where t1.DATA_TYPE = 'CHAR'

) loop

execute immediate 'alter table ' || c.table_name || ' add g_w_' || c.column_name || ' varchar2(' || c.char_length || ')';

execute immediate 'update ' || c.table_name || ' set g_w_' || c.column_name || ' = ' || c.column_name;

execute immediate 'update ' || c.table_name || ' set ' || c.column_name || ' = null';

execute immediate 'alter table ' || c.table_name || ' modify ' || c.column_name || ' varchar2(' || c.char_length || ')';

execute immediate 'update ' || c.table_name || ' set ' || c.column_name || ' = trim(g_w_' || c.column_name || ')';

execute immediate 'alter table ' || c.table_name || ' drop column g_w_' || c.column_name;

end loop;

end;

How to remove space-like characters in Oracle database table structure

1. First, remove spaces before comparison (for example, remove all spaces in names):

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

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

Then comparison can be made.

2. However, we may find that there are characters similar to spaces in the middle or at the end of some names that have not been removed, but they are obviously not ordinary spaces, so we suspect that Tab Jian is at fault. So I followed the method of removing the TAB key mentioned on the Internet and used chr(9) to replace the space bar (the ASCII value of the TAB key is 9). First take a record with space-like characters for experiment:

SELECT replace(xm, chr(9) ) from table_name where;

Using this method will remove some real tab key spaces, and then adopt a batch update method.

3. But the situation I encountered was not so lucky and I failed to remove the space-like key. How to do it? I decided to try to get the ASCII code value of this space key first, and then use the chr (ASCII code value) method to process it.

①. Obtain the length () of the entire string containing the space-like key, thereby determining the starting position of the space-like key in the string and the length of the space-like key.

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

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

The problem is solved. But I found that the ASCII code value obtained in step 2 above was 41377. This should be the ASCII code value of a Chinese character, and it looked like a space. So I wondered, could it be the space key and TAB key in Chinese mode? After trying it I found out it wasn't. I also wonder, is it the difference between full-width and half-width? Because we usually enter characters at half-width. I followed the first step "1. First remove the spaces before comparing..." and tried to enter the spaces in the full-width state and search. The result was a surprise. It turned out that all the spaces that were not removed were the spaces entered in the full-width state. The parallel connection confirmed that the space entered in full-width mode has an ASCII code value of 41377.

The above is the detailed content of What is the solution to the problem of spaces in CHAR type data in Oracle?. For more information, please follow other related articles on the PHP Chinese website!