SQL经典案例之提取字符串的第 N 个子串

SQL经典案例之提取第n个子串

• 问题描述
• 构造测试数据
• Oracle
• instr / substr
• MySQL
• substring_index
• PostgreSQL
• split_part

问题描述

将字符串按分隔符提取第n个字符串，下面案例以获取第二个子字符串为例

--------提取前------- >>>> -------提取后---------
      OLD_MIXED            	NEW_MIXED
-------------------- >>>> ---------------------
Database
apple;orange		    orange
xshell;CRT;mobaxterm	    CRT
TEST;DBA;DEV;BAT	    DBA
abc;def;ghi;jkl;mno	    def
-------------------- >>>> ---------------------
复制

构造测试数据

-- drop table IF EXISTS t_string;
drop table t_string;
create table t_string(tid int,tmixed varchar(40));
insert into t_string values(1,'Database');
insert into t_string values(2,'apple;orange');
insert into t_string values(3,'xshell;CRT;mobaxterm');
insert into t_string values(4,'TEST;DBA;DEV;BAT');
insert into t_string values(5,'abc;def;ghi;jkl;mno');

commit;
复制

Oracle

instr / substr

用 instr 函数定位所要提取字符串的分隔符[ ; ] 位置，再用 substr 函数提取该字符串

col col1 format 9999
col col2 format 9999
col col3 format a15

select tmixed,instr(tmixed,';',1) as col1,instr(tmixed,';',1,2) as col2,substr(tmixed,instr(tmixed,';',1)+1,instr(tmixed,';',1,2)-instr(tmixed,';',1,1)-1) as col3 from t_string;
复制

此处由于数据非分号结尾导致当数据是两个字符串时，instr定位第二个分隔符位置失败，对此可以先统一数据格式：给每个数据末尾加个分号的分隔符

select ts.tmixed from (select tmixed || ';' as tmixed from t_string) ts;

select tmixed,instr(tmixed,';',1) as col1,instr(tmixed,';',1,2) as col2,substr(tmixed,instr(tmixed,';',1)+1,instr(tmixed,';',1,2)-instr(tmixed,';',1,1)-1) as col3 from (select tmixed||';' as tmixed from t_string) ts;
复制

MySQL

substring_index

 substring_index（'源字符串'，'截取指定的分隔符'，分隔符的位置）
复制

MySQL的 substring_index 函数可以截取指定分隔符前面的子字符串

select tmixed,substring_index(tmixed,';',2) as col1,substring_index(substring_index(tmixed,';',2),';',-1) as col2 from t_string; 
复制

先按分隔符截取前两个子字符串再取后一个字符串，当数据存在无分隔符仅有一个字符串的场景该方式截取就有问题，因无指定的分隔符故取了字符串全部，对此可以先再末尾都拼接个分隔符再做截取操作

select tmixed,tmixed||';' as tmixed1,concat(tmixed,';') as tmixed2 from t_string;

select tmixed,substring_index(tmixed,';',2) as col1,substring_index(substring_index(tmixed,';',2),';',-1) as col2 from (select concat(tmixed,';') as tmixed from t_string) ts;
复制

在MySQL里拼接字符串不能使用双竖线||，可以改用 concat 函数

PostgreSQL

split_part

PostgreSQL的 split_part 函数可从指定字符串中按分隔符切割并获取特定子字符串部分

SELECT tmixed,split_part(tmixed, ';', 2) as col1,split_part(tmixed, ';', -1) as col2 from t_string; 
复制

自定义MySQL的substring_index函数：

CREATE OR REPLACE FUNCTION substring_index(varchar, varchar, integer)
RETURNS varchar AS $$
DECLARE
	tokens varchar[];
	length integer ;
	indexnum integer;
BEGIN
	tokens := pg_catalog.string_to_array($1, $2);
	length := pg_catalog.array_upper(tokens, 1);
	indexnum := length - ($3 * -1) + 1;
	IF $3 >= 0 THEN
		RETURN pg_catalog.array_to_string(tokens[1:$3], $2);
	ELSE
		RETURN pg_catalog.array_to_string(tokens[indexnum:length], $2);
	END IF;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
复制