trim 函数的使用

trim函数是用来去除列数据中前面和后面的空格。

LTrim(string)：去除字符串前面的空格或者说是左边的空格。

RTrim(string)：去除字符串后面的空格或者说是去除右边的空格。

Trim(string)：去除字符串左边和右边的空格。

SQL> insert into yy values('a ');
 
1 row inserted
 
SQL> select dump(name) from yy;
 
DUMP(NAME)
--------------------------------------------------------------------------------
Typ=1 Len=2: 97,32
 
SQL> insert into yy values(' a ');
 
1 row inserted
 
SQL> select dump(name) from yy;
 
DUMP(NAME)
--------------------------------------------------------------------------------
Typ=1 Len=2: 97,32
Typ=1 Len=3: 32,97,32
 
SQL> insert into yy values('a ');
 
1 row inserted
 
SQL> select dump(name) from yy;
 
DUMP(NAME)
--------------------------------------------------------------------------------
Typ=1 Len=2: 97,32
Typ=1 Len=3: 32,97,32
Typ=1 Len=2: 97,32
 
SQL> insert into yy values(' a');
 
1 row inserted
 
SQL> select dump(name) from yy;
 
DUMP(NAME)
--------------------------------------------------------------------------------
Typ=1 Len=2: 97,32
Typ=1 Len=3: 32,97,32
Typ=1 Len=2: 97,32
Typ=1 Len=2: 32,97

SQL> select *  from yy;
 
NAME
--------------------
a
 a
a
 a
 我们的测试数据已经插入完成；

SQL> update yy set name=ltrim(name);
 
4 rows updated
 
SQL> select dump(name) from yy;
 
DUMP(NAME)
--------------------------------------------------------------------------------
Typ=1 Len=2: 97,32
Typ=1 Len=2: 97,32
Typ=1 Len=2: 97,32
Typ=1 Len=1: 97

去除了字符串左边的空格

SQL> update yy set name=rtrim(name);
 
4 rows updated
 
SQL> select dump(name) from yy;
 
DUMP(NAME)
--------------------------------------------------------------------------------
Typ=1 Len=1: 97
Typ=1 Len=2: 32,97
Typ=1 Len=1: 97
Typ=1 Len=2: 32,97

结论：去除字符串右边的空格 

SQL> rollback;
 
Rollback complete
 
SQL> update yy set name=trim(name);
 
4 rows updated
 
SQL> select dump(name) from yy;
 
DUMP(NAME)
--------------------------------------------------------------------------------
Typ=1 Len=1: 97
Typ=1 Len=1: 97
Typ=1 Len=1: 97
Typ=1 Len=1: 97

结论去除字符串两边的空格
 
SQL> rollback;
 
Rollback complete