Hive SQL 精进系列：SUBSTR 函数的多样用法

目录

• • 一、引言
  • 二、SUBSTR 函数基础介绍
  • • 2.1 基本语法
    • 2.2 参数详解
    • 2.3 简单示例
  • 三、SUBSTR 函数常见应用场景
  • • 3.1 提取日期中的年份、月份或日期
    • 3.2 隐藏部分敏感信息
  • 四、SUBSTR 函数高级用法
  • • 4.1 结合条件判断动态截取
    • 4.2 处理复杂字符串模式
  • 五、总结

一、引言

SUBSTR 函数是 Hive SQL 中一个用于字符串截取的重要函数，在处理文本数据时发挥着关键作用。本文将全面且深入地介绍 Hive SQL 中 SUBSTR 函数的基本语法、常见应用场景以及一些高级用法。

二、SUBSTR 函数基础介绍

2.1 基本语法

SUBSTR 函数主要用于从字符串中提取指定位置和长度的子字符串。它有两种常见的语法形式：

— 形式一：指定起始位置和截取长度
SUBSTR(str, pos, len)

— 形式二：只指定起始位置，截取从起始位置到字符串末尾的所有字符
SUBSTR(str, pos)

2.2 参数详解

• str：这是要进行截取操作的原始字符串。它可以是一个字符串常量，也可以是表中的某个字符串类型的字段。
• pos：表示截取的起始位置。需要注意的是，在 Hive SQL 中，字符串的索引是从 1 开始的。如果 pos 为正数，那么截取将从字符串的左边开始计数；如果 pos 为负数，截取将从字符串的右边开始计数。
• len：这是一个可选参数，用于指定要截取的子字符串的长度。如果省略该参数，则默认截取从起始位置到字符串末尾的所有字符。

2.3 简单示例

为了更好地理解 SUBSTR 函数的基本用法，我们先创建一个简单的示例表并插入一些数据：

— 创建示例表
CREATE TABLE text_data (
id INT,
full_name STRING
);

— 插入示例数据
INSERT INTO text_data VALUES
(1, 'John Doe'),
(2, 'Jane Smith'),
(3, 'Bob Johnson');

现在，我们可以使用 SUBSTR 函数来截取 full_name 字段中的姓氏。假设姓氏是名字中的最后一个单词，我们可以从倒数第一个空格之后开始截取：

— 截取姓氏
SELECT
id,
full_name,
SUBSTR(full_name, INSTR(full_name, ' ') + 1) AS last_name
FROM
text_data;

在上述代码中，INSTR(full_name, ' ') 用于查找字符串中第一个空格的位置，然后 SUBSTR 函数从该位置的下一个字符开始截取，直到字符串末尾，从而得到姓氏。运行该查询后，我们将得到如下结果：

三、SUBSTR 函数常见应用场景

3.1 提取日期中的年份、月份或日期

在处理日期数据时，经常需要从日期字符串中提取年份、月份或日期。假设我们有一个包含日期信息的表 date_table，日期格式为 YYYY-MM-DD：

— 创建日期表
CREATE TABLE date_table (
record_id INT,
date_str STRING
);

— 插入示例数据
INSERT INTO date_table VALUES
(1, '2024-05-15'),
(2, '2025-03-20'),
(3, '2026-07-10');

— 提取年份
SELECT
record_id,
date_str,
SUBSTR(date_str, 1, 4) AS year,
SUBSTR(date_str, 6, 2) AS month,
SUBSTR(date_str, 9, 2) AS day
FROM
date_table;

在上述代码中，通过 SUBSTR 函数分别截取日期字符串的不同部分，从而得到年份、月份和日期。运行该查询后，结果如下：

3.2 隐藏部分敏感信息

在处理包含敏感信息的数据时，为了保护数据安全，我们可能需要隐藏部分信息。例如，隐藏手机号码的中间几位：

— 创建包含手机号码的表
CREATE TABLE phone_numbers (
user_id INT,
phone_number STRING
);

— 插入示例数据
INSERT INTO phone_numbers VALUES
(1, '13800138000'),
(2, '13900139000'),
(3, '15000150000');

— 隐藏手机号码中间四位
SELECT
user_id,
CONCAT(SUBSTR(phone_number, 1, 3), '****', SUBSTR(phone_number, 8)) AS masked_phone
FROM
phone_numbers;

在上述代码中，首先使用 SUBSTR 函数截取手机号码的前三位和后四位，然后使用 CONCAT 函数将它们与 **** 拼接起来，从而实现隐藏中间四位的效果。运行该查询后，结果如下：

四、SUBSTR 函数高级用法

4.1 结合条件判断动态截取

在实际应用中，我们可能需要根据不同的条件动态地进行字符串截取。例如，对于不同长度的字符串，采用不同的截取策略：

— 创建包含不同长度字符串的表
CREATE TABLE variable_length_text (
text_id INT,
text_content STRING
);

— 插入示例数据
INSERT INTO variable_length_text VALUES
(1, 'Short text'),
(2, 'This is a much longer text'),
(3, 'A medium length text');

— 根据字符串长度动态截取
SELECT
text_id,
text_content,
CASE
WHEN LENGTH(text_content) <= 10 THEN text_content
WHEN LENGTH(text_content) <= 20 THEN SUBSTR(text_content, 1, 10)
ELSE SUBSTR(text_content, 1, 15)
END AS truncated_text
FROM
variable_length_text;

在上述代码中，使用 CASE 语句结合 LENGTH 函数根据字符串的长度进行判断，然后使用 SUBSTR 函数进行不同长度的截取。运行该查询后，结果如下：

4.2 处理复杂字符串模式

当字符串中存在复杂的模式时，SUBSTR 函数可以与其他字符串函数结合使用来提取所需信息。例如，从包含版本号的字符串中提取主版本号和次版本号：

— 创建包含版本号的表
CREATE TABLE version_info (
app_id INT,
version_str STRING
);

— 插入示例数据
INSERT INTO version_info VALUES
(1, '1.2.3'),
(2, '2.0.1'),
(3, '3.1.5');

— 提取主版本号和次版本号
SELECT
app_id,
version_str,
SUBSTR(version_str, 1, INSTR(version_str, '.') – 1) AS major_version,
SUBSTR(version_str, INSTR(version_str, '.') + 1, INSTR(SUBSTR(version_str, INSTR(version_str, '.') + 1), '.') – 1) AS minor_version
FROM
version_info;

在上述代码中，首先使用 INSTR 函数找到第一个 . 的位置，然后使用 SUBSTR 函数截取主版本号。对于次版本号，先截取第一个 . 之后的字符串，再使用 INSTR 函数找到新字符串中 . 的位置，最后使用 SUBSTR 函数截取次版本号。运行该查询后，结果如下：

五、总结

本文详细介绍了 Hive SQL 中 SUBSTR 函数的基本语法、常见应用场景以及高级用法。SUBSTR 函数作为一个强大的字符串截取工具，在处理各种文本数据时都能发挥重要作用。无论是简单的日期提取、敏感信息隐藏，还是复杂的字符串模式处理，合理运用 SUBSTR 函数都能帮助我们高效地完成数据处理任务。在实际工作中可以根据具体需求灵活运用 SUBSTR 函数，并结合其他字符串函数，以实现更加复杂和精细的数据处理逻辑。