substr、replace函数简单应用

substr、replace函数简单应用

ORACLE接触的久了，我的大脑也开始遵循LRU原则，不常用的知识很快就会被刷新掉，为了和ORACLE一样保证数据一致性，只好将这些东西保存到硬盘上。
前段时间数据已经加载到数据库，最近一直做的是整理这些数据，SUBSTR和REPLACE函数用的比较多， 这里简单记录下。
有一张存放图片的表，包含以下字段，IDENTIFIER是档号，JPG_PATH是图片的路径和名字，其他字段这里没有用到，不做描述。

SQL> select id,IDENTIFIER,JPG_PATH from images where rownum<11;
      ID   IDENTIFIER    JPG_PATH
--------   -----------   ----------------------------------
       1                 /waiwubu/0220/18-0220-003-0001.JPG
       2                 /waiwubu/0221/18-0221-003-0001.JPG
       3                 /waiwubu/0221/18-0221-003-0002.JPG
       4                 /waiwubu/0221/18-0221-003-0003.JPG
       5                 /waiwubu/0223/18-0223-005-0001.JPG
       6                 /waiwubu/0223/18-0223-005-0002.JPG
       7                 /waiwubu/0223/18-0223-005-0003.JPG
       8                 /waiwubu/0223/18-0223-005-0004.JPG
       9                 /waiwubu/0223/18-0223-005-0005.JPG
      10                 /waiwubu/0223/18-0223-005-0006.JPG
10 rows selected.

图片是用SQLLDR工具加载到数据库，夹在是只知道图片的路径和名字，并不知道档号信息，但是知道图片名字去除最后一个”-”后面的部分就是档号，而且档号字段用来和其他表关联，必须要有档号的信息，就需要利用JPG_PATH字段来生成档号信息，就需要利用截断函数SUBSTR了，这里SUBSTR函数的用法如下：

SQL> update images set identifier=substr(jpg_path,15,11) where rownum<11;
10 rows updated.

这里SUBSTR函数用到了3个参数，jpg_path是要截取的字段名字，后面的“15”是表示从第几位开始截取，如果后面不再加数值，表示一直截取到最后，这里的第三个参数“11”表示一共截取多少位，也就是截取多长的意思，如下：

QL> select id,IDENTIFIER,JPG_PATH from images where rownum<11;
      ID   IDENTIFIER    JPG_PATH
--------   -----------   ----------------------------------
       1   18-0220-003   /waiwubu/0220/18-0220-003-0001.JPG
       2   18-0221-003   /waiwubu/0221/18-0221-003-0001.JPG
       3   18-0221-003   /waiwubu/0221/18-0221-003-0002.JPG
       4   18-0221-003   /waiwubu/0221/18-0221-003-0003.JPG
       5   18-0223-005   /waiwubu/0223/18-0223-005-0001.JPG
       6   18-0223-005   /waiwubu/0223/18-0223-005-0002.JPG
       7   18-0223-005   /waiwubu/0223/18-0223-005-0003.JPG
       8   18-0223-005   /waiwubu/0223/18-0223-005-0004.JPG
       9   18-0223-005   /waiwubu/0223/18-0223-005-0005.JPG
      10   18-0223-005   /waiwubu/0223/18-0223-005-0006.JPG
10 rows selected.

这里如果不加第三个参数“11”的话，结果会从第15位一直截取到最后，结果如下：

SQL> update images set identifier=substr(jpg_path,15) where rownum<11;
10 rows updated.
SQL> select id,IDENTIFIER,JPG_PATH from images where rownum<11;
      ID   IDENTIFIER             JPG_PATH
--------   --------------------   ----------------------------------
       1   18-0220-003-0001.JPG   /waiwubu/0220/18-0220-003-0001.JPG
       2   18-0221-003-0001.JPG   /waiwubu/0221/18-0221-003-0001.JPG
       3   18-0221-003-0002.JPG   /waiwubu/0221/18-0221-003-0002.JPG
       4   18-0221-003-0003.JPG   /waiwubu/0221/18-0221-003-0003.JPG
       5   18-0223-005-0001.JPG   /waiwubu/0223/18-0223-005-0001.JPG
       6   18-0223-005-0002.JPG   /waiwubu/0223/18-0223-005-0002.JPG
       7   18-0223-005-0003.JPG   /waiwubu/0223/18-0223-005-0003.JPG
       8   18-0223-005-0004.JPG   /waiwubu/0223/18-0223-005-0004.JPG
       9   18-0223-005-0005.JPG   /waiwubu/0223/18-0223-005-0005.JPG
      10   18-0223-005-0006.JPG   /waiwubu/0223/18-0223-005-0006.JPG
10 rows selected.

在更新完图片表的档号字段后，文件表根据档号字段就可以好图片表关联查找到图片信息，但是测试后发现，个别条目数据并不能挂载到图片，经查发现是文件表的档号字段由于历史数据错误，部分存在空格，如下：

SQL> select ''''||IDENTIFIER||'''' from t_file_info where IDENTIFIER like '% % 
and rownum<2;
IDENTIFIER
---------------
'18-0223-005  '

由于存在多余的空格，在图片表里查询不到这些有空格的档号，所以会挂接不上，这样就需要将这些多余的空格替换掉，这里用到了REPLACE函数。

SQL> update t_file_info set identifier=replace(identifier,' ','') 
where IDENTIFIER like '% % and rownum<2;
1 rows updated.

这里REPLACE函数也用到了3个参数，第一个参数（identifier）是要替换的字段名字，第二个参数（’ ‘)，是要替换的内容，这里是空格，第三个字段（‘’）是替换成什么内容，这里两个单引号之间什么也没有，表示将空格替换成空，也就相当于将空格删除，替换后数据如下：

SQL> select ''''||IDENTIFIER||'''' from t_file_info where IDENTIFIER like '% % 
and rownum<2;
IDENTIFIER
---------------
'18-0223-005'

删除掉多余的空格，条目数据已经可以和图片正确挂接。

建议继续学习：