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

一、引言

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 函数，并结合其他字符串函数，以实现更加复杂和精细的数据处理逻辑。