SQL 语句中的函数

与很多编程语言一样，SQL 也支持函数，为数据的转换和处理提供了方法，比如前面我们用到的 RTRIM 函数去掉文本右侧可能存在的空白字符。

注：对于不同的 DBMS 各个函数的名称和用法可能及其不同，只有很少的函数被所有主要的 DBMS 支持。本篇文章使用的演示 DBMS 为 SQLite，练习前请使用 SELECT 语句检测你的环境是否支持相关函数。

今天，我们主要学习两类常用的函数：文本处理函数和日期、时间函数。

文本处理函数

前面我们提到的 RTRIM LTRIM TRIM 都是非常常见的文本处理函数。下面是小鱼总结的 SQL 常用文本处理函数。

函数	说明
LEFT() / RIGHT()	返回字符串左边 / 右边的字符。
LENGTH() / DATALENGTH() / LEN()	返回字符串的长度。
LOWER() / UPPER()	将字符串转换为小写 / 大写。
LTRIM() / RTRIM() / TRIM()	去掉字符串左边 / 右边 / 两边的空格。
SUBSTR() / SUBSTRING()	提取子字符串

下面，小鱼先演示一下 UPPER 函数的使用。从供应商表中检索出供应商名称以及供应商名称的大写形式，并将大写的列命名为 vend_name_upcase 。

SELECT
    vend_name,
    UPPER( vend_name ) AS vend_name_upcase 
FROM
    Vendors 
ORDER BY
    vend_name;

检索结果：

UPPER 函数将供应商的名称转换为了大写形式。

下面，小鱼再来举个文本函数的例子。字符串截取函数 SUBSTR SUBSTRING ，使用 SELECT 语句来检测一下 SQLite 是否支持 SUBSTR 函数。

SELECT
    SUBSTR( 'abcdef', 1, 2 ),
    SUBSTR( 'abcdef', 1, 3 ),
    SUBSTR( 'abcdef', 3 );

上述 SQL 没有报错，并返回了如下的结果：

第一个参数 abcdef 表示待截取的文本，第二个参数为从第几个字符开始截取，最后一个参数为截取多少位。最后一个参数省略时将截取之后的所有长度。

然后，我们使用 SUBSTR 函数从 cust_contact 中取取前 2 个字符，从 cust_city 中提取 3 个字符，将他们转换为大写形式，并拼接在一起，以 user_login 别名返回。

客户信息表如下：

编写 SQL 检索出客户 ID 、客户名称以及登录名 user_login：

SELECT
    cust_id,
    cust_name,
    UPPER(
    SUBSTR( cust_contact, 1, 2 ) || SUBSTR( cust_city, 1, 3 )) AS user_login 
FROM
    Customers;

检索结果：

日期、时间处理函数

日期、时间类型作为特殊的类型存储在表中，每种 DBMS 都以各自的特殊形式进行存储，以便进行快速、有效的排序或过滤，并且占用更少的空间存储。

我们需要借助日期、时间函数来读取、统计和处理这些日期、时间，因此日期、时间函数在 SQL 中有着非常重要的作用。不过，感到遗憾的是，不同的 DBMS 这些日期时间函数相差很大，可移植性最差。

比如，可以使用如下语句检测一下你的数据库是否支持使用 CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP 来获取当前日期、时间、日期时间。

SELECT 
    CURRENT_DATE,
    CURRENT_TIME,
    CURRENT_TIMESTAMP;

下面是小鱼的 SQLite 数据库返回的结果：

使用 STRFTIME 可以将表中的日期时间类型转换为指定格式的日期时间字符串。在不支持使用 YEAR DATEPART 和 EXTRACT 函数的 DBMS 中，还可以用 STRFTIME 提取年、月等日期时间的成分。

SELECT 
    STRFTIME( '%Y/%m/%d %H:%M:%S', CURRENT_TIMESTAMP ),
    STRFTIME( '%Y', CURRENT_TIMESTAMP ),
    STRFTIME( '%m', CURRENT_TIMESTAMP );

下面是小鱼的 SQLite 数据库返回的结果：

函数 DATETIME DATE 可以将日期时间格式的字符串转换为表中的日期时间类型，以便进行过滤和运算。

SELECT 
    DATETIME( '2022-03-13 05:44:19' ),
    DATE('2022-03-13');

下面是小鱼的 SOLite 数据库 DATE DATETIME 返回结果。

最后，我们来看几个案例，案例使用的表格为下面的订单表：

首先，我们需要检索出 2020 年 2 月份的所有订单编号和订单日期：

SELECT
  order_num,
  order_date 
FROM
  Orders 
WHERE
  STRFTIME( '%Y/%m', order_date ) = '2020/02';

上述 SQL 语句中，将表中的日期类型 order_date 日期转换为 %Y/%m 格式的日期字符串，然后利用字符串的比较来筛选出 2020 年 02 月份的订单。检索结果如下：

前面，小鱼说表中存储的日期时间类型可以很方便地进行排序和过滤。下面我们就先来看看过滤吧！使用 SQL 语句筛选出 2020 年 1 月 15 号至 2020 年 2 月 15 号的所有订单：

SELECT
    order_num,
    order_date 
FROM
    Orders 
WHERE
    order_date BETWEEN DATE( '2020-01-15' ) 
    AND DATE( '2020-02-15' );

使用 DATE() 函数可以将时间格式的字符串转换为表中的日期类型，日期类型同样可以用于比较操作符和 BETWEEN 等。检索结果如下：

日期时间类型也可以用于排序的依据：

SELECT
    order_num,
    order_date 
FROM
    Orders 
WHERE
    STRFTIME( '%Y%m', order_date ) = '202001' 
ORDER BY
    order_date;

按升序方式返回的 2020 年 1 月份订单：

总结

本节，我们主要学习了 SQL 中常用的文本处理函数以及时间、日期处理函数。这些函数在格式化、数据过滤等方面非常重要，但比较遗憾的是它们在不同的 DBMS 中实现很不一致，可移植性差。