PostgreSQL 字符串函数和操作符

PostgreSQL 提供了丰富的字符串函数和操作符，用于检查和操作字符串值。这些函数适用于 character、character varying 和 text 类型，是数据处理和查询中不可或缺的工具。

字符串类型处理原则

INFO

字符串类型转换规则

除非另有说明，所有函数都声明为接受并返回 text 类型
character varying 参数可以互换使用
character 类型的值在应用函数前会转换为 text，自动去除尾随空格

SQL 标准字符串函数和操作符

字符串连接操作

基本连接运算符 `||`

字符串连接是最常用的操作之一，PostgreSQL 的 || 运算符功能强大且灵活。

基本连接实际应用场景

sql

-- 字符串与字符串连接
SELECT 'Post' || 'greSQL' AS result;
-- 结果: PostgreSQL

-- 字符串与数字连接
SELECT 'Value: ' || 42 AS result;
-- 结果: Value: 42

-- 多个字符串连接
SELECT 'Hello' || ' ' || 'World' || '!' AS greeting;
-- 结果: Hello World!

sql

-- 构建用户全名
SELECT
    first_name || ' ' || last_name AS full_name
FROM users;

-- 构建邮箱地址
SELECT
    username || '@' || domain AS email
FROM user_accounts;

-- 构建文件路径
SELECT
    base_path || '/' || folder || '/' || filename AS full_path
FROM file_system;

连接运算符 `||` 会自动将非字符串类型转换为文本，但不支持数组类型。如需连接数组的文本表示，需要显式转换为 `text`。

字符串修剪函数

`btrim()` - 双端修剪

sql

-- 基础语法
btrim(string text [, characters text]) → text

-- 实际应用示例

基本用法数据清洗场景

sql

-- 去除两端空格（默认行为）
SELECT btrim('  hello world  ') AS result;
-- 结果: 'hello world'

-- 去除指定字符
SELECT btrim('xyxtrimyyx', 'xyz') AS result;
-- 结果: 'trim'

-- 去除多种字符
SELECT btrim('###***Hello World***###', '#*') AS result;
-- 结果: 'Hello World'

sql

-- 清理用户输入的姓名
UPDATE users
SET name = btrim(name, ' \t\n\r')
WHERE name IS NOT NULL;

-- 清理产品代码中的特殊字符
SELECT
    product_id,
    btrim(product_code, '[](){}') AS clean_code
FROM products;

-- 处理 CSV 导入数据
SELECT
    btrim(column_value, ' "''') AS cleaned_value
FROM imported_csv_data;

`ltrim()` 和 `rtrim()` - 单端修剪

左侧修剪右侧修剪实际应用

sql

-- 去除左侧指定字符
SELECT ltrim('zzzytest', 'xyz') AS result;
-- 结果: 'test'

-- 去除左侧空格
SELECT ltrim('   leading spaces') AS result;
-- 结果: 'leading spaces'

sql

-- 去除右侧指定字符
SELECT rtrim('testxxzx', 'xyz') AS result;
-- 结果: 'test'

-- 去除右侧空格
SELECT rtrim('trailing spaces   ') AS result;
-- 结果: 'trailing spaces'

sql

-- 处理日志文件中的时间戳
SELECT
    ltrim(log_timestamp, '0') AS cleaned_timestamp
FROM system_logs
WHERE log_timestamp LIKE '00%';

-- 清理产品名称右侧的版本号
SELECT
    rtrim(product_name, '0123456789.v') AS base_product_name
FROM products;

字符串填充函数

`lpad()` 和 `rpad()` - 字符串填充

字符串填充在报表格式化和数据对齐中非常有用。

左填充 lpad右填充 rpad实际应用场景

sql

-- 基本语法
lpad(string text, length integer [, fill text]) → text

-- 示例
SELECT lpad('hi', 5, 'xy') AS result;
-- 结果: 'xyxhi'

SELECT lpad('123', 8, '0') AS padded_number;
-- 结果: '00000123'

sql

-- 基本语法
rpad(string text, length integer [, fill text]) → text

-- 示例
SELECT rpad('hi', 5, 'xy') AS result;
-- 结果: 'hixyx'

SELECT rpad('Name', 20, '.') AS formatted_name;
-- 结果: 'Name................'

sql

-- 格式化报表中的账户号码
SELECT
    account_id,
    lpad(account_number::text, 12, '0') AS formatted_account
FROM accounts;

-- 创建对齐的产品清单
SELECT
    rpad(product_name, 30, '.') || ' ' ||
    lpad(price::text, 10, ' ') AS product_line
FROM products
ORDER BY product_name;

-- 生成固定宽度的 ID
SELECT
    'USER' || lpad(user_id::text, 6, '0') AS formatted_id
FROM users;

字符串长度函数

字符长度字节长度位长度

sql

-- character_length() - 字符数量
SELECT character_length('josé') AS char_count;
-- 结果: 4

SELECT char_length('Hello 世界') AS char_count;
-- 结果: 8

sql

-- octet_length() - 字节数量
SELECT octet_length('josé') AS byte_count;
-- 结果: 5 (UTF8编码)

SELECT octet_length('Hello 世界') AS byte_count;
-- 结果: 11 (UTF8编码)

sql

-- bit_length() - 位数量
SELECT bit_length('jose') AS bit_count;
-- 结果: 32 (4个字符 × 8位)

在多字节编码（如 UTF-8）中，字符长度和字节长度可能不同。处理国际化数据时要特别注意这一点。

字符串提取函数

`substring()` - 子字符串提取

substring() 函数提供了多种提取子字符串的方式：

位置提取正则表达式提取实际应用

sql

-- 从指定位置开始提取
SELECT substring('Thomas' from 2 for 3) AS result;
-- 结果: 'hom'

SELECT substring('Thomas' from 3) AS result;
-- 结果: 'omas'

SELECT substring('Thomas' for 2) AS result;
-- 结果: 'Th'

sql

-- 使用 POSIX 正则表达式
SELECT substring('Thomas' from '...$') AS result;
-- 结果: 'mas'

-- 提取邮箱域名
SELECT substring('[email protected]' from '@(.*)') AS domain;
-- 结果: 'example.com'

sql

-- 提取文件扩展名
SELECT
    filename,
    substring(filename from '\.([^.]+)$') AS file_extension
FROM uploaded_files;

-- 提取电话号码的区号
SELECT
    phone_number,
    substring(phone_number from '^(\d{3})') AS area_code
FROM contacts;

-- 提取产品代码的类别部分
SELECT
    product_code,
    substring(product_code from 1 for 3) AS category_code
FROM products;

`overlay()` - 字符串替换

sql

-- 基本语法
overlay(string text PLACING newsubstring text FROM start integer [FOR count integer]) → text

-- 示例
SELECT overlay('Txxxxas' placing 'hom' from 2 for 4) AS result;
-- 结果: 'Thomas'

基本替换数据修正场景

sql

-- 替换指定位置的字符
SELECT overlay('Hello World' placing 'XXX' from 7 for 5) AS result;
-- 结果: 'Hello XXX'

-- 插入字符（不删除原字符）
SELECT overlay('Hello World' placing 'Beautiful ' from 7 for 0) AS result;
-- 结果: 'Hello Beautiful World'

sql

-- 修正错误的产品代码
UPDATE products
SET product_code = overlay(product_code placing 'PRD' from 1 for 3)
WHERE product_code LIKE 'XXX%';

-- 匿名化敏感信息
SELECT
    customer_name,
    overlay(phone_number placing '****' from 4 for 4) AS masked_phone
FROM customers;

字符串搜索函数

`position()` - 查找子字符串位置

sql

-- 基本语法
position(substring text IN string text) → integer

-- 示例
SELECT position('om' in 'Thomas') AS position;
-- 结果: 3

基本搜索实际应用

sql

-- 查找子字符串位置
SELECT position('SQL' in 'PostgreSQL') AS pos;
-- 结果: 7

-- 查找不存在的子字符串
SELECT position('xyz' in 'PostgreSQL') AS pos;
-- 结果: 0

sql

-- 检查邮箱格式
SELECT
    email,
    CASE
        WHEN position('@' in email) > 0 THEN '有效'
        ELSE '无效'
    END AS email_status
FROM user_accounts;

-- 分析日志中的错误位置
SELECT
    log_message,
    position('ERROR' in upper(log_message)) AS error_position
FROM system_logs
WHERE upper(log_message) LIKE '%ERROR%';

其他重要字符串函数

字符串格式化 - `format()` 函数

format() 函数是 PostgreSQL 中最强大的字符串格式化工具，类似于 C 语言的 sprintf 函数。

格式说明符

说明符	类型	描述
`%s`	字符串	简单字符串输出
`%I`	SQL 标识符	安全的标识符引用
`%L`	SQL 字面量	安全的字面量引用
`%%`	字面量	输出单个 % 字符

基本格式化SQL安全格式化位置参数

sql

-- 简单字符串格式化
SELECT format('Hello %s', 'World') AS greeting;
-- 结果: Hello World

-- 多参数格式化
SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three') AS result;
-- 结果: Testing one, two, three, %

sql

-- 使用 %I 格式化标识符（防SQL注入）
SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly') AS sql;
-- 结果: INSERT INTO "Foo bar" VALUES('O''Reilly')

-- 格式化文件路径
SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:\Program Files') AS sql;
-- 结果: INSERT INTO locations VALUES('C:\Program Files')

sql

-- 使用位置参数
SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three') AS result;
-- 结果: Testing three, two, one

-- 混合使用位置参数和顺序参数
SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three') AS result;
-- 结果: Testing three, two, three

宽度控制和对齐

右对齐（默认）左对齐实际应用

sql

-- 固定宽度，右对齐
SELECT format('|%10s|', 'foo') AS result;
-- 结果: |       foo|

-- 动态宽度
SELECT format('|%*s|', 10, 'foo') AS result;
-- 结果: |       foo|

sql

-- 使用 - 标志左对齐
SELECT format('|%-10s|', 'foo') AS result;
-- 结果: |foo       |

-- 动态宽度左对齐
SELECT format('|%-*s|', 10, 'foo') AS result;
-- 结果: |foo       |

sql

-- 生成对齐的报表
SELECT
    format('%-20s | %10s | %15s',
           product_name,
           format('%8.2f', price),
           format('%10d', stock_quantity)
    ) AS product_report
FROM products
ORDER BY product_name;

-- 构建动态SQL查询
SELECT format(
    'SELECT %s FROM %I WHERE %I = %L',
    string_agg(column_name, ', '),
    table_name,
    'id',
    search_value
) AS dynamic_query
FROM information_schema.columns
WHERE table_name = 'users'
GROUP BY table_name;

字符串连接函数

`concat()` 和 `concat_ws()` 函数

concat函数concat_ws函数实际应用

sql

-- 连接多个值，忽略NULL
SELECT concat('abcde', 2, NULL, 22) AS result;
-- 结果: 'abcde222'

-- 连接用户信息
SELECT concat(first_name, ' ', last_name, ' (', user_id, ')') AS user_info
FROM users;

sql

-- 使用分隔符连接，忽略NULL
SELECT concat_ws(',', 'abcde', 2, NULL, 22) AS result;
-- 结果: 'abcde,2,22'

-- 构建CSV格式数据
SELECT concat_ws('|',
    customer_id,
    customer_name,
    phone,
    email,
    city
) AS csv_line
FROM customers;

sql

-- 构建面包屑导航
SELECT concat_ws(' > ', category, subcategory, product_name) AS breadcrumb
FROM products;

-- 生成用户显示名称
SELECT
    user_id,
    concat_ws(' ',
        CASE WHEN title IS NOT NULL THEN title END,
        first_name,
        middle_name,
        last_name,
        CASE WHEN suffix IS NOT NULL THEN suffix END
    ) AS display_name
FROM users;

字符串替换和操作

`replace()` - 字符串替换

基本替换数据清理场景

sql

-- 替换所有匹配的子字符串
SELECT replace('abcdefabcdef', 'cd', 'XX') AS result;
-- 结果: 'abXXefabXXef'

-- 清理数据中的特殊字符
SELECT replace(replace(user_input, '<', '&lt;'), '>', '&gt;') AS safe_html
FROM user_comments;

sql

-- 标准化电话号码格式
UPDATE contacts
SET phone = replace(replace(replace(phone, '-', ''), ' ', ''), '(', '');

-- 清理产品描述中的多余空格
UPDATE products
SET description = replace(description, '  ', ' ')
WHERE description LIKE '%  %';

`regexp_replace()` - 正则表达式替换

基本正则替换复杂模式处理

sql

-- 替换第一个匹配项
SELECT regexp_replace('Thomas', '.[mN]a.', 'M') AS result;
-- 结果: 'ThM'

-- 替换所有匹配项（使用g标志）
SELECT regexp_replace('Hello World Hello', 'Hello', 'Hi', 'g') AS result;
-- 结果: 'Hi World Hi'

sql

-- 格式化电话号码
SELECT regexp_replace('1234567890', '(\d{3})(\d{3})(\d{4})', '(\1) \2-\3') AS formatted_phone;
-- 结果: '(123) 456-7890'

-- 清理HTML标签
SELECT regexp_replace(content, '<[^>]+>', '', 'g') AS plain_text
FROM articles;

-- 标准化空白字符
SELECT regexp_replace(description, '\s+', ' ', 'g') AS clean_description
FROM products;

字符串分割函数

`split_part()` - 分割字符串

基本分割实际应用

sql

-- 获取指定位置的部分
SELECT split_part('abc~@~def~@~ghi', '~@~', 2) AS result;
-- 结果: 'def'

-- 从末尾倒数获取部分
SELECT split_part('abc,def,ghi,jkl', ',', -2) AS result;
-- 结果: 'ghi'

sql

-- 从邮箱地址提取用户名和域名
SELECT
    email,
    split_part(email, '@', 1) AS username,
    split_part(email, '@', 2) AS domain
FROM user_accounts;

-- 解析文件路径
SELECT
    file_path,
    split_part(file_path, '/', -1) AS filename,
    split_part(split_part(file_path, '/', -1), '.', -1) AS extension
FROM uploaded_files;

`string_to_array()` - 字符串转数组

基本转换数据处理场景

sql

-- 分割为数组
SELECT string_to_array('xx~~yy~~zz', '~~', 'yy') AS result;
-- 结果: {xx,NULL,zz}

-- 处理标签字符串
SELECT string_to_array('web,mobile,api,database', ',') AS tags
FROM projects;

sql

-- 解析配置选项
SELECT
    config_name,
    unnest(string_to_array(config_values, ';')) AS individual_value
FROM system_configurations;

-- 处理多选字段
SELECT
    user_id,
    unnest(string_to_array(interests, ',')) AS interest
FROM user_profiles
WHERE interests IS NOT NULL;

字符串编码和转换

`ascii()` 和 `chr()` - 字符编码转换

字符到编码编码到字符实际应用

sql

-- 获取字符的ASCII值
SELECT ascii('A') AS ascii_value;
-- 结果: 65

SELECT ascii('中') AS unicode_value;
-- 结果: 20013 (Unicode码点)

sql

-- 从ASCII值获取字符
SELECT chr(65) AS character;
-- 结果: 'A'

SELECT chr(20013) AS chinese_char;
-- 结果: '中'

sql

-- 生成字母序列
SELECT chr(ascii('A') + generate_series(0, 25)) AS alphabet
FROM generate_series(0, 25);

-- 编码敏感数据
SELECT
    customer_id,
    string_agg(chr(ascii(substring(customer_name, i, 1)) + 1), '')
FROM customers,
     generate_series(1, length(customer_name)) AS i
GROUP BY customer_id, customer_name;

高级字符串操作

`reverse()` - 字符串反转

基本反转实际应用

sql

SELECT reverse('abcde') AS result;
-- 结果: 'edcba'

SELECT reverse('PostgreSQL') AS result;
-- 结果: 'LQSergsoP'

sql

-- 检查回文字符串
SELECT
    word,
    CASE
        WHEN lower(word) = lower(reverse(word)) THEN '是回文'
        ELSE '不是回文'
    END AS palindrome_check
FROM word_list;

-- 创建倒序索引用于后缀搜索
CREATE INDEX idx_customer_name_reverse
ON customers(reverse(lower(customer_name)));

`translate()` - 字符转换

字符映射数据清理应用

sql

-- 字符一对一替换
SELECT translate('12345', '143', 'ax') AS result;
-- 结果: 'a2x5'

-- 删除字符（目标字符串较短）
SELECT translate('hello world', 'lo', 'X') AS result;
-- 结果: 'heXX wXrXd'

sql

-- 清理电话号码中的格式字符
SELECT translate(phone_number, '()-. ', '') AS clean_phone
FROM contacts;

-- 标准化产品代码
SELECT translate(upper(product_code), 'OILSZ', '01152') AS normalized_code
FROM products;

实际业务场景应用

用户数据处理

用户信息格式化数据验证

sql

-- 创建用户显示信息
SELECT
    user_id,
    -- 格式化用户名
    concat_ws(' ',
        initcap(btrim(first_name)),
        initcap(btrim(last_name))
    ) AS display_name,

    -- 格式化邮箱
    lower(btrim(email)) AS clean_email,

    -- 格式化电话号码
    CASE
        WHEN length(translate(phone, '()-. ', '')) = 10 THEN
            format('(%s) %s-%s',
                substring(translate(phone, '()-. ', ''), 1, 3),
                substring(translate(phone, '()-. ', ''), 4, 3),
                substring(translate(phone, '()-. ', ''), 7, 4)
            )
        ELSE phone
    END AS formatted_phone
FROM users;

sql

-- 验证邮箱格式
SELECT
    email,
    CASE
        WHEN position('@' in email) > 1
         AND position('@' in email) < length(email)
         AND position('.' in split_part(email, '@', 2)) > 0
        THEN '有效'
        ELSE '无效'
    END AS email_validation
FROM user_accounts;

日志分析

日志解析错误日志分析

sql

-- 解析访问日志
SELECT
    log_time,
    split_part(log_line, ' ', 1) AS ip_address,
    split_part(log_line, '"', 2) AS request_method,
    split_part(split_part(log_line, '"', 2), ' ', 2) AS request_path,
    split_part(log_line, ' ', -2)::integer AS status_code
FROM access_logs
WHERE log_line IS NOT NULL;

sql

-- 提取错误信息
SELECT
    log_time,
    CASE
        WHEN position('ERROR' in upper(message)) > 0 THEN 'ERROR'
        WHEN position('WARNING' in upper(message)) > 0 THEN 'WARNING'
        WHEN position('INFO' in upper(message)) > 0 THEN 'INFO'
        ELSE 'UNKNOWN'
    END AS log_level,

    -- 提取错误代码
    regexp_replace(message, '.*ERROR\s+(\d+):.*', '\1') AS error_code,

    -- 清理消息内容
    btrim(regexp_replace(message, '\s+', ' ', 'g')) AS clean_message
FROM system_logs;

数据导入清理

CSV数据清理

sql

-- 清理导入的CSV数据
WITH cleaned_data AS (
    SELECT
        -- 清理产品名称
        btrim(regexp_replace(product_name, '\s+', ' ', 'g')) AS clean_name,

        -- 标准化价格格式
        replace(replace(price_string, '$', ''), ',', '')::numeric AS price,

        -- 清理描述
        btrim(regexp_replace(
            replace(description, '"', ''),
            '\s+', ' ', 'g'
        )) AS clean_description,

        -- 解析标签
        string_to_array(
            lower(btrim(tags)),
            ',',
            ''
        ) AS tag_array
    FROM imported_products
)
SELECT * FROM cleaned_data
WHERE clean_name IS NOT NULL AND price > 0;

性能优化建议

TIP

性能优化要点

索引优化：为经常搜索的字符串字段创建适当的索引
函数索引：对经常使用字符串函数的查询创建函数索引
批量操作：使用 UPDATE 语句进行批量字符串处理
正则表达式：复杂的字符串处理可以考虑使用正则表达式函数

索引优化批量处理

sql

-- 为字符串搜索创建索引
CREATE INDEX idx_product_name_lower ON products(lower(product_name));
CREATE INDEX idx_email_domain ON users(split_part(email, '@', 2));

-- 使用函数索引
CREATE INDEX idx_phone_clean ON contacts(translate(phone, '()-. ', ''));

sql

-- 批量清理数据
UPDATE products
SET
    product_name = btrim(regexp_replace(product_name, '\s+', ' ', 'g')),
    description = btrim(description)
WHERE product_name != btrim(regexp_replace(product_name, '\s+', ' ', 'g'))
   OR description != btrim(description);

在大数据表上使用字符串函数时要注意性能影响。复杂的正则表达式和字符串操作可能会消耗大量 CPU 资源。

总结

PostgreSQL 的字符串函数和操作符为数据处理提供了强大的工具集。通过合理使用这些函数，可以：

数据清理：去除多余空格、标准化格式
数据验证：检查邮箱、电话号码等格式
报表格式化：创建美观的输出格式
动态 SQL 构建：安全地构建动态查询
日志分析：解析和分析各种日志格式

掌握这些字符串函数将显著提升你的 PostgreSQL 数据处理能力。在实际应用中，要根据具体需求选择合适的函数，并注意性能优化。

PostgreSQL 字符串函数和操作符 ​

字符串类型处理原则 ​

SQL 标准字符串函数和操作符 ​

字符串连接操作 ​

基本连接运算符 || ​

字符串修剪函数 ​

btrim() - 双端修剪 ​

ltrim() 和 rtrim() - 单端修剪 ​

字符串填充函数 ​

lpad() 和 rpad() - 字符串填充 ​

字符串长度函数 ​

字符串提取函数 ​

substring() - 子字符串提取 ​

overlay() - 字符串替换 ​

字符串搜索函数 ​

position() - 查找子字符串位置 ​

其他重要字符串函数 ​

字符串格式化 - format() 函数 ​

格式说明符 ​

宽度控制和对齐 ​

字符串连接函数 ​

concat() 和 concat_ws() 函数 ​

字符串替换和操作 ​

replace() - 字符串替换 ​

regexp_replace() - 正则表达式替换 ​

字符串分割函数 ​

split_part() - 分割字符串 ​

string_to_array() - 字符串转数组 ​

字符串编码和转换 ​

ascii() 和 chr() - 字符编码转换 ​

高级字符串操作 ​

reverse() - 字符串反转 ​

translate() - 字符转换 ​

实际业务场景应用 ​

用户数据处理 ​

日志分析 ​

数据导入清理 ​

性能优化建议 ​

总结 ​

PostgreSQL 字符串函数和操作符

字符串类型处理原则

SQL 标准字符串函数和操作符

字符串连接操作

基本连接运算符 `||`

字符串修剪函数

`btrim()` - 双端修剪

`ltrim()` 和 `rtrim()` - 单端修剪

字符串填充函数

`lpad()` 和 `rpad()` - 字符串填充

字符串长度函数

字符串提取函数

`substring()` - 子字符串提取

`overlay()` - 字符串替换

字符串搜索函数

`position()` - 查找子字符串位置

其他重要字符串函数

字符串格式化 - `format()` 函数

格式说明符

宽度控制和对齐

字符串连接函数

`concat()` 和 `concat_ws()` 函数

字符串替换和操作

`replace()` - 字符串替换

`regexp_replace()` - 正则表达式替换

字符串分割函数

`split_part()` - 分割字符串

`string_to_array()` - 字符串转数组

字符串编码和转换

`ascii()` 和 `chr()` - 字符编码转换

高级字符串操作

`reverse()` - 字符串反转

`translate()` - 字符转换

实际业务场景应用

用户数据处理

日志分析

数据导入清理

性能优化建议

总结