序言

背景说明

清晨醒来就写了一篇关于前几日做错题的博文，博闻强记，加深印象，增加视野，所以这里又想着写一篇关于INSTR相关或者相近的内置函数的使用。针对INSTR内置函数时针对字符串的操作，所以这里也想介绍一下其他的字符串相关的内置函数。例如

Oracle 数据库提供了丰富的内置函数，涵盖数值处理、字符串操作、日期和时间处理、逻辑判断、集合处理、数据分析、数据类型转换等多个方面。下面就随着我一起来学习下这个内置函数吧，有解释不到之处，还望批评指正。

示例环境

本篇示例是基于Oracle DB 19c EE （19.17.0.0.0）版本操作，如果有不同之处，请指出。

内置函数

1 字符串长度-LENGTH

【定义】

LENGTH(string)：返回字符串的字符数量，例如字符串“abc”的长度为 3，则返回 3 。

【示例】

select LENGTH('我是字符串') from dual;

2 字符串截取-SUBSTR

【定义】

SUBSTR(string, start[, length])：提取字符串的一部分。

【参数】

• string：在哪一个字符串中提取，查找。
• start：在string字符串中开始查找的位置。
• length（可选）：查找的长度。

【示例】

例如查找字符串（我是字符串）从第2个字符开始查找，查找长度默认从第二个开始，到字符串结束位置。如果明确了长度，那么从第2个字符开始查找，往后查找的长度为3后的字符串。

select 
    SUBSTR('我是字符串', 2) default_1,
    SUBSTR('我是字符串', 2, 3) default_2
from dual;

3 字符串查找-INSTR

相关内容请参考上一篇博文，上一篇博文做了详细的介绍以及使用情况。

4 字符串大小写转换

I 转换为大/小写字符

【定义】

UPPER(string)：将字符串转换为全大写。

LOWER(string)：将字符串转换为全小写。

【示例】

select 
    UPPER('I am Aion, I come from Guangzhou') default_1,
    LOWER('I am Aion, I come from Guangzhou') default_2
from dual;

II 每个单词首字母大写

【定义】

INITCAP(string)：将字符串中每个单词的首字母转换为大写字母。

【示例】

select 
    INITCAP('I am Aion, I come from Guangzhou') default_1
from dual;

5 字符串替换

【定义】

REPLACE(string, old_pattern, new_pattern)：替换字符串中的子串，这个内置函数比较常用。

REGEXP_REPLACE(string, pattern, replacement[, flags])：使用正则表达式替换字符串中的子串，使用场景例如一些编号，新系统与旧系统的编号不一致，但是数据都在一起的情况，此时可以使用这个内置函数处理下后展示。

【示例】

select 
    REPLACE('I am Aion, I come from Guangzhou', 'Guangzhou', 'Tsingtao') default_1,
    REGEXP_REPLACE('I am Aion, I come from Guangzhou, My phone number is 13800138000', '\d', '*') default_2
from dual;

6 字符串拼接-CONCAT

【定义】

CONCAT(string1, string2, ...) ：连接多个字符串，也是一个比较常用的内置函数，尤其是很多会在代码中直接写，当然你也可以这样子写：string1 || string2。

【示例】

select
    CONCAT('I am Aion', 'I come from Guangzhou') column_1,
    'I am Aion'||'I come from Guangzhou' column_2
from dual;

7 字符串填充

字符串填充，同我们在Excel中处理字符差不多，可以从左边开始，也可以从右边开始（或者说倒序处理）

【定义】

LPAD(string, length[, pad_char])： 左侧填充字符至指定长度。

RPAD(string, length[, pad_char])：右侧填充字符至指定长度。

【参数】

• string：原字符串。
• length：填充到指定长度（下面填充长度）。

• 如果填充长度等于原字符串长度，则返回原字符串；
• 如果填充长度小于原字符串长度，则不填充且截取原字符串到填充长度；
• 如果填充长度大于原字符串长度，则需要补充的长度为原字符串+填充字符串长度（多余截取）。

• pad_char：填充的字符串。

【示例一】

如果填充长度等于原字符串长度，则返回原字符串。

select
    LENGTH('I am Aion') column_len,
    LPAD('I am Aion', 14, 'Hello') column_L,
    RPAD('I am Aion', 14, 'Hello') column_R
from dual;

【示例二】

例如原字符串长度为9，使用LPAD函数左填充到11长度，填充字符串为「hello」，那么返回的结果为原字符串（I am Aion）长度9补充到11长度，那么就要从字符串（hello）中再拿俩字符串来填充到11长度位数。相反RPAD也是这个道理。

select
    LENGTH('I am Aion') column_len,
    LPAD('I am Aion', 11, 'Hello') column_L,
    RPAD('I am Aion', 16, 'Hello') column_R
from dual;

8 字符串去除

I 去除左/右指定字符串

【定义】

LTRIM(string[, trim_chars])：去除字符串左侧指定字符。

RTRIM(string[, trim_chars])：去除字符串右侧指定字符。

【参数】

• string：原始字符串。
• trim_chars：需要删除的指定的字符，默认不填写则删除空格，遇到非空格则停止删除。

【示例】

默认不指定需要删除的字符串，则先删除空格，遇到非空格则停止删除，返回删除空格后的字符串。

select
    LTRIM('   不装了，摊 牌 了，这题目一直迷惑我，做错了') column_L,
    RTRIM('不装了，摊牌了，这 题 目 一 直 迷 惑 我，做错了   ') column_R
from dual;

指定需要删除的字符串，按整体匹配到则删除，匹配不到则停止删除，返回删除空格后的字符串。

II 去除指定字符串

【定义】

TRIM([leading|trailing|both] [trim_chars] FROM string)：从指定位置移除指定字符。

【参数】

• leading：指定从字符串的开头删除匹配字符。
• trailing：指定从字符串的结尾删除匹配字符。
• both：或者如果不指明任何位置，则指定从字符串的两端删除匹配字符。
• trim_chars：是要被删除的字符或字符集。如果不指定此参数，则默认为空格。
• string：是需要被处理的字符串或数值。

【示例】

默认情况下，TRIM内置函数是去掉左右两边的空格，遇到非空格则停止去除动作，返回去除空格后的字符串。

select 
    TRIM(' 不装了，摊 牌 了  ') default_1,
    TRIM(leading ' ' FROM ' 不装了，摊 牌 了  ') default_2,
    TRIM(trailing ' ' FROM ' 不装了，摊 牌 了  ') default_3,
    TRIM(both ' ' FROM ' 不装了，摊 牌 了  ') default_4,
    TRIM(both 'x' FROM 'x不装了，摊x牌x了xx') default_5
from dual;

从上面的实验示例来看：

default_1是去掉了两边的空格，执行的是默认情况；

default_2是从头部开始去除空格；

default_3是从尾部开始去除空格；

default_4是从两边开始去除空格（头部和尾部都去掉空格，遇到非空格则停止执行，返回结果）；

default_5是从两边开始去除字符x，这个结果同default_4。

总结

有些内置函数可以在SELECT语句中用于处理数据，同时也可以在WHERE等语句中使用。今天了解到的函数在处理数据库中的字符串数据时非常有用，特别是当需要转换、去除、清理或格式化数据时。具体是如何使用，都要考虑使用场景以及性能方面的问题，后续会再出一些相关的博文。