汉字和字符分开（整理）

SQL> create table gjtext
  2  ( name varchar2(100));

Table created

SQL> insert into gjtext values(镀锌钢管 100*6m');

1 row inserted

。。。。。。

1 row inserted

SQL> commit;

SQL> select * from gjtext;

NAME
--------------------------------------------------------------------------------
镀锌钢管 100*6m
镀锌钢管  10
镀锌钢管  100
镀锌钢管 125
镀锌钢管  15
镀锌钢管 150
镀锌钢管 150*6m
螺旋管 150*6m
螺旋管 70
螺旋管 89

 

方法一：

SQL> select substr(name,1,instr(name,'管')) as a_name,substr(name,instr(name,'管')+1) as a_standard from gjtext;

A_NAME                                                                           A_STANDARD
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
镀锌钢管                                                                           100*6m
镀锌钢管                                                                          10
镀锌钢管                                                                           100
镀锌钢管                                                                          125
镀锌钢管                                                                         15
镀锌钢管                                                                        150
镀锌钢管                                                                          150*6m
螺旋管                                                                          150*6m
螺旋管                                                                            70
螺旋管                                                                           89

10 rows selected

 

方法二：

 SQL> select substr(name,1,lengthb(name)-length(name)) item,
  2  substr(name,lengthb(name)-length(name)+1) item_style
  3  from gjtext;

ITEM                                                                             ITEM_STYLE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
镀锌钢管                                                                           100*6m
镀锌钢管                                                                         10
镀锌钢管                                                                          100
镀锌钢管                                                                          125
镀锌钢管                                                                           15
镀锌钢管                                                                         150
镀锌钢管                                                                          150*6m
螺旋管                                                                          150*6m
螺旋管                                                                           70
螺旋管                                                                            89

10 rows selected

 

方法三：正则表达式

SQL> select name,substr(name,0,regexp_instr(name,'d')-1) name,substr(name,regexp_instr(name,'d')) guige from gjtext;

ITEM                                                                             ITEM_STYLE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
镀锌钢管                                                                           100*6m
镀锌钢管                                                                         10
镀锌钢管                                                                          100
镀锌钢管                                                                          125
镀锌钢管                                                                           15
镀锌钢管                                                                         150
镀锌钢管                                                                          150*6m
螺旋管                                                                          150*6m
螺旋管                                                                           70
螺旋管                                                                            89

10 rows selected