文档首页/ 数据仓库服务 DWS/ SQL语法参考/ SQL语法参考(9.1.0.x)/ 函数和操作符/ 二进制字符串函数和操作符

更新时间：2026-10-28 GMT+08:00

二进制字符串函数和操作符

在数据仓库的数据处理过程中，用户经常需要操作二进制字符串（bytea类型）数据。例如，处理包含特殊字符或非文本数据的字段时，需要截取二进制子串、查找特定字节序列的位置、替换部分内容，或对二进制数据进行逐位/逐字节的精确操作。此外，从外部系统导入的数据可能包含分隔符分隔的复合字符串，需要按分隔符提取子串。

SQL标准定义了一部分二进制字符串函数，这些函数使用关键字（如from、placing、in）而不是逗号来分隔参数。DWS在此基础上提供了更多使用常规逗号分隔参数语法的扩展函数，覆盖更丰富的二进制字符串操作场景。本文档将系统介绍DWS提供的二进制字符串函数和操作符，帮助用户在实际业务中正确选择和使用这些函数。

基本概念

bytea类型：DWS中的二进制数据类型，用于存储原始字节数据。bytea类型的数据以\x开头的十六进制格式显示，例如\x546f6d表示字符串”Tom”的字节序列。在SQL语句中，可以使用E'\\xxx'::bytea语法定义bytea字面量，其中\\xxx为八进制转义表示。
SQL关键字语法：SQL标准定义的部分二进制字符串函数使用关键字而非逗号分隔参数。例如substring(string from int for int)使用from和for关键字指定起始位置和长度，position(substring in string)使用in关键字指定搜索范围。这些函数是SQL标准的一部分，兼容性较好。
函数调用语法：DWS扩展的二进制字符串函数使用常规的逗号分隔参数语法。例如get_bit(string, offset)、btrim(string, bytes)等，与普通函数调用方式一致。
二进制字符串与普通字符串的区别：普通字符串（text/varchar）按字符处理，受字符集编码影响；二进制字符串（bytea）按字节处理，不受字符集编码影响，适用于存储和操作任意二进制数据。

二进制字符串函数分类

DWS二进制字符串函数按功能可分为以下5类：

表1 函数分类
分类	包含函数	说明
长度查询函数	octet_length(string)	查询二进制字符串的字节数。
查找与截取函数	position(substring in string)、substring(string [from int] [for int])、substring_index(string, delim, count)	查找子串位置、截取子串。
替换函数	overlay(string placing string from int [for int])	替换二进制字符串中的子串。
裁剪函数	trim([both] bytes from string)、btrim(string bytea, bytes bytea)	从首尾删除指定字节。
位与字节操作函数	get_bit(string, offset)、get_byte(string, offset)、set_bit(string, offset, newvalue)、set_byte(string, offset, newvalue)	逐位/逐字节读取和修改。

长度查询函数

octet_length(string)

描述：二进制字符串中的字节数。

返回值类型：integer

示例：

     SELECT octet_length(E'jo\\000se'::bytea) AS RESULT;
 result
--------
      5
(1 row)
 
 
  

查找与截取函数

position(substring in string)

描述：返回特定子字符串在二进制字符串中的起始位置（从1开始计数）。未找到时返回0。

返回值类型：integer

示例：

     --返回子串\000om在Th\000omas中的起始位置（从1开始计数）,结果为3，表示\000om从第3个字节开始（T=第1字节, h=第2字节, \000=第3字节）。
SELECT position(E'\\000om'::bytea in E'Th\\000omas'::bytea) AS RESULT;
 result
--------
      3
(1 row)
 
 
  

substring(string [from int] [for int])

描述：从二进制字符串中截取子串。from指定起始位置（从1开始计数），for指定截取长度。省略from时从第1个字节开始，省略for时截取到字符串末尾。

返回值类型：bytea

示例：

     --截取第2~4字节，即 68 00 6F，结果为\x68006f，十六进制表示截取到的3个字节（68='h', 00=空字节, 6f='o'）
SELECT substring(E'Th\\000omas'::bytea from 2 for 3) AS RESULT; 
  result  
----------
 \x68006f
(1 row)
 
 
  

截取时间，获取小时数：

     SELECT substring('2022-07-18 14:38:15',12,2)AS RESULT;
 result
-----------
 14
(1 row)
 
 
  

substring_index(string, delim, count)

描述：按照区分大小写匹配查找delimiter分隔符，返回string字符串中第count次出现delim分隔符之前的子串。若count为负数，则从末尾向前查找delim分隔符。若参数含有NULL值，返回NULL。该函数仅8.2.0及以上集群版本支持。

返回值类型：text

示例：按照区分大小写匹配查找delimiter分隔符".wWw."，返回string字符串"www.wWw.cloud.wWw.com"中第2次出现delimiter分隔符之前的子串"www.wWw.cloud"。

     SELECT SUBSTRING_INDEX('www.wWw.cloud.wWw.com', '.wWw.', 2) AS RESULT;
    result
---------------
 www.wWw.cloud
(1 row)
 
 
  

替换函数

overlay(string placing string from int [for int])

描述：替换二进制字符串中的子串。将string中从from指定位置开始的子串替换为placing指定的字符串。for指定被替换子串的长度，省略时替换长度等于placing字符串的长度。

返回值类型：bytea

示例：

     --从第2个字节开始，用替换串(02 03)替换原串中3个字节(68 00 6F)的内容，结果为\x5402036d6173，原串第2~4字节(68 00 6F)被替换为(02 03)，结果为 54 02 03 6D 61 73
SELECT overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 2 for 3) AS RESULT;
     result     
----------------
 \x5402036d6173
(1 row)
 
 
  

裁剪函数

trim([both] bytes from string)

描述：从string的开头和结尾删除只包含bytes中字节的最长字符串。both关键字可省略，表示同时裁剪首尾。

返回值类型：bytea

示例：

     --删除原串开头和结尾的\000空字节，输出：\x546f6d，即"Tom"（54='T', 6f='o', 6d='m'），首尾的\000空字节已删除
SELECT trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea) AS RESULT;
  result  
----------
 \x546f6d
(1 row)
 
 
  

btrim(string bytea,bytes bytea)

描述：从string的开头和结尾删除只包含bytes中字节的最长的字符串。功能与trim([both] bytes from string)相同，但使用逗号分隔参数的函数调用语法。

返回值类型：bytea

示例：

     --删除原串开头和结尾的\000空字节，功能同trim但使用逗号分隔参数语法，输出：\x7472696d，即"trim"（74='t', 72='r', 69='i', 6d='m'），首尾的\000空字节已删除
SELECT btrim(E'\\000trim\\000'::bytea, E'\\000'::bytea) AS RESULT;
   result   
------------
 \x7472696d
(1 row)
 
 
  

位与字节操作函数

以下函数支持对二进制字符串进行逐位或逐字节的精确读取和修改。offset参数从0开始计数。

get_bit(string, offset)

描述：从字符串中抽取指定位置的一个位（bit），返回值为0或1。

返回值类型：integer

示例：

     --获取第45位的值（0或1），输出：1，表示第45位的值为1（第5字节0x61=01100001，第5位=1）
SELECT get_bit(E'Th\\000omas'::bytea, 45) AS RESULT; 
 result
--------
      1
(1 row)
 
 
  

get_byte(string, offset)

描述：从字符串中抽取指定位置的一个字节，返回值为该字节的ASCII码（0-255）。

返回值类型：integer

示例：

     -- 输入：E'Th\\000omas'（字节序列：54 68 00 6F 6D 61 73），offset=4（第5个字节，0开始计数）
-- 功能：获取offset=4位置的字节值
SELECT get_byte(E'Th\\000omas'::bytea, 4) AS RESULT; 
 result
--------
    109
(1 row)
-- 输出：109，即字符'm'的ASCII码值（0x6D = 十进制109）
 
 
  

set_bit(string,offset, newvalue)

描述：设置字符串中指定位置的位值。newvalue为0或1。返回修改后的新字符串，原字符串不变。

返回值类型：bytea

示例：

     -- 输入：E'Th\\000omas'（54 68 00 6F 6D 61 73），offset=45（第5字节第5位），newvalue=0
-- 功能：将第45位的值从1设置为0
SELECT set_bit(E'Th\\000omas'::bytea, 45, 0) AS RESULT; 
      result      
------------------
 \x5468006f6d4173
(1 row)
-- 输出：\x5468006f6d4173，第5字节由0x61('a')变为0x41('A')，即第5位从1变为0
 
 
  

set_byte(string,offset, newvalue)

描述：设置字符串中指定位置的字节值。newvalue为0-255的整数。返回修改后的新字符串，原字符串不变。

返回值类型：bytea

示例：

     -- 输入：E'Th\\000omas'（54 68 00 6F 6D 61 73），offset=4（第5个字节），newvalue=64
-- 功能：将第5个字节的值从0x6D(109)修改为64(0x40='@')
SELECT set_byte(E'Th\\000omas'::bytea, 4, 64) AS RESULT; 
      result      
------------------
 \x5468006f406173
(1 row)
-- 输出：\x5468006f406173，第5字节由6D变为40，即'm'变为'@'
 
 
  

父主题：函数和操作符

上一篇：判断某个字符串是否符合某种匹配模式(字符串相关的模式匹配函数)

下一篇：位串函数和操作符

二进制字符串函数和操作符

基本概念

二进制字符串函数分类

长度查询函数

octet_length(string)

查找与截取函数

position(substring in string)

substring(string [from int] [for int])

substring_index(string, delim, count)

替换函数

overlay(string placing string from int [for int])

裁剪函数

trim([both] bytes from string)

btrim(string bytea,bytes bytea)

位与字节操作函数

get_bit(string, offset)

get_byte(string, offset)

set_bit(string,offset, newvalue)

set_byte(string,offset, newvalue)

您对此文档页面的整体评价？