PostgreSQL 14.9：如何筛选仅含一个正斜杠的数据行

问题场景 #

现有数据行如下：

'abcd/12313:100', 
'abcd/12123:1001/XYS/ABCDXY',
'abcd/12123:1001/XYS/ABCDXY/TEYE'

需要筛选出仅包含一个正斜杠的行（即第一行），但尝试以下SQL语句后，返回了所有含斜杠的行，未得到预期结果：

select distinct id from public.table_name where id ~* '[/{0,1}]';

select distinct id from public.table_name where id ~* '/';

使用环境为PostgreSQL 14.9。

错误原因 #

• 第一个正则表达式[/{0,1}]写法错误：方括号内是字符集合，这里实际是匹配/、{、0、,、1、}中的任意一个字符，完全不符合"仅一个斜杠"的匹配逻辑。
• 第二个语句~* '/'仅匹配包含至少一个斜杠的行，无法区分斜杠数量。

解决方案 #

以下三种方法均可实现需求：

方法1：正则表达式精确匹配 #

使用正则限制整个字符串仅含一个斜杠：

SELECT DISTINCT id FROM public.table_name WHERE id ~* '^[^/]*\/[^/]*$';

原理：^匹配字符串开头，[^/]*匹配任意数量的非斜杠字符，\/匹配唯一的斜杠，[^/]*匹配斜杠后的非斜杠字符，$匹配字符串结尾，确保没有多余的斜杠。

方法2：统计斜杠出现次数（正则函数） #

用REGEXP_COUNT函数直接统计斜杠数量：

SELECT DISTINCT id FROM public.table_name WHERE REGEXP_COUNT(id, '/') = 1;

原理：REGEXP_COUNT会返回字符串中匹配指定模式的次数，等于1即符合条件。

方法3：字符串长度差计算 #

通过字符串长度差值计算斜杠数量，性能优于正则：

SELECT DISTINCT id FROM public.table_name WHERE LENGTH(id) - LENGTH(REPLACE(id, '/', '')) = 1;

原理：原字符串长度减去移除所有斜杠后的字符串长度，差值就是斜杠的总数量，等于1即为目标行。

内容的提问来源于stack exchange，提问作者Macky