SQL去除括号及括号内内容：正则与字符串函数的详细指南

上周我接了一个数据清洗需求某张表里带括号的字段要去掉括号本身也要把括号里的内容一并删掉。需求一句话SQL看起来也不难可当我真的写SQL去掉括号和括号里的内容时踩了整整一下午的坑。这个场景在数据分析、报表开发、接口对接里太常见了——渠道名后面挂着设备、商品名后面挂着规格、客户名后面挂着部门全是括号。今天这篇就当是踩坑流水账把不同数据库的写法、正则的细节、以及那些让你怎么删都删不干净的坑一次讲清楚。适合正在跟脏数据搏斗的SQL入门者也适合帮维护人员写清洗脚本的开发者。1. 先想清楚需求长什么样三种最常见的括号残留场景动手写SQL之前我建议你先花两分钟盘一下数据到底长什么样。同样叫去掉括号和括号里的内容实际要处理的情况差别很大。我现实中见过最多的是下面这三种。1.1 场景一渠道或来源名称后面挂着设备备注很多埋点表里会这样存自然搜索(移动端)、竞价(PC)、直接访问(微信内)。分析的时候按渠道维度汇总自然搜索(移动端)和自然搜索(PC端)会被当成两个渠道整个看板就裂了。这时候需要把括号和括号里的设备标记删掉让它们归并成一个渠道。这种字段的特点是括号一定出现在字符串的末尾括号后面没有其他有效内容而且一列数据里的括号可能是中文括号也可能是英文括号。处理起来最省事但直接用SUBSTRING_INDEX这类函数切就行了不一定非要上正则。1.2 场景二商品名或规格字段把说明性文字塞进括号比如商品表里的苹果(5斤装)、苹果(礼盒)、茅台(飞天53度)前端要展示纯净的商品名导出报表时也不能带括号备注。还有更复杂的苹果(山东烟台红富士)这种产地加规格全塞进一个括号括号中间还可能嵌套一个括号比如苹果(山东(烟台))。这种字段的麻烦点在于括号不一定在结尾也可能出现在中间比如苹果(5斤装)现货。如果只按取第一个左括号前的内容来切会把现货也丢掉这就是典型的清洗事故。1.3 场景三两个字段被粗心人拼进同一列还有一种常见情况报表系统导出的时候把备注拼进来了张三(销售部)、李四(技术部)实际上前面是姓名括号里是部门。这种字段的目标通常是保留姓名把部门删掉。但我也想提醒一句清洗之前别闷头就删。先问一句括号里的内容真的没用吗——我接过一个需求对方说要删括号内容结果括号里是员工工号删完才发现工号是唯一能关联主数据的字段最后又从备份里捞回来重跑。凡是清理类任务先备份永远是第一步。另外动手之前还要明确一个变体你是想把所有括号对都删掉还是只删第一个出现的括号对这俩在SQL写起来完全不是一回事。如果需求是每个字段最多一个括号那后面的写法都够用如果字段里可能有好几对括号就得用全局替换的方案。2. 低版本MySQL的土办法字符串函数硬切先说一下不依赖正则的方案。很多人还在用MySQL 5.7甚至更老的版本REGEXP_REPLACE在8.0以前压根不存在这种情况下就只能用字符串函数硬切。硬切虽然笨但某些简单场景下性能不一定差理解它也有助于你搞明白正则到底帮你省了什么。2.1 括号固定在末尾SUBSTRING_INDEX 一行搞定如果确认括号一定在字符串末尾比如自然搜索(移动端)思路非常简单取第一个左括号左边的内容然后TRIM掉多余空格。SELECT TRIM(SUBSTRING_INDEX( REPLACE(REPLACE(source_name, , (), , )), (, 1 )) AS cleaned_name FROM channel_table;这里先用两个REPLACE把中文左括号统一成英文左括号(把中文右括号统一成英文右括号)然后再按左括号切。这样做的好处是无论原始数据是全角还是半角都能一刀切干净。这个方案有几个前提必须说清楚第一括号后面不能有需要保留的内容否则SUBSTRING_INDEX(source_name, (, 1)会把后面的内容全部丢掉第二它只认第一个左括号如果字段里存在多个括号对也只保留第一个左括号之前的部分第三如果字段里压根没有括号SUBSTRING_INDEX会返回整个字段这个特性在某些场景下是安全的但如果你后续要拼接内容就要用CASE WHEN先判断有没有括号。2.2 括号出现在中间先定位再拼接如果括号不在末尾比如苹果(5斤装)现货就不能用SUBSTRING_INDEX一刀切了需要自己算位置。SQL的思路是先找到第一个左括号的位置lp再找到从lp开始往后的第一个右括号的位置rp然后把字符串拆成两段——左括号之前的部分和右括号之后的部分中间删除。SELECT CONCAT( LEFT(norm, LOCATE((, norm) - 1), SUBSTRING(norm, LOCATE(), norm, LOCATE((, norm)) 1) ) AS cleaned_name FROM ( SELECT REPLACE(REPLACE(source_name, , (), , )) AS norm FROM channel_table ) t WHERE norm LIKE %(%;解释一下这段逻辑。内层子查询把全角括号统一成半角LOCATE((, norm)是第一个左括号的位置LOCATE(), norm, LOCATE((, norm))是从左括号的位置开始往后找第一个右括号的位置。LEFT取出左括号左边的内容SUBSTRING取出右括号右边的内容中间那段括号连同内容就被拼掉了。这个写法只删除第一对括号。如果字段里有第二个括号对第一次执行完它还在你得再执行一次。更关键的是它假设第一个右括号就是第一个左括号的配对。遇到苹果(山东(烟台))这种嵌套结构它会把(山东(烟台)删掉留下一个不配对的右括号结果变成苹果)完全不是你要的东西。所以用字符串函数硬切一定要先确认数据里没有嵌套括号。2.3 这种方案的边界在哪里把字符串函数方案总结一下它适合括号结构简单、位置固定、数量有限的清洗任务。它的优点是不依赖数据库版本、不依赖正则扩展、逻辑直接缺点是遇到多重括号、嵌套括号、中英文括号混用的时候代码会膨胀到很难维护而且每多一种情况就要多写一层判断。我自己的经验是如果你的数据表里能拍胸脯保证最多一对括号且不嵌套那用字符串函数完全没问题但如果你连自己都不确定括号长什么样建议直接跳到下一章的REGEXP_REPLACE正则表达式才是处理这类文本的通用方案。3. REGEXP_REPLACE 的正确打开方式以及它为什么能通用MySQL 8.0开始提供了REGEXP_REPLACE函数这是目前处理去掉括号和括号里的内容最顺手的方法也是我日常最常用的方案。一条SQL就能覆盖中英文括号、多对括号、括号在任意位置的情况。3.1 一条正则[(][^()]*[)]直接给结论MySQL 8.0这么写SELECT REGEXP_REPLACE( source_name, [(][^()]*[)], ) AS cleaned_name FROM channel_table;这个正则拆开看就三块[(]匹配一个左括号中文的或者英文的(都算[^()]*匹配除了括号以外的任意字符的零个或多个。注意这里有个取反符号^意思是只要不是中英文括号中间出现什么字符都能匹配包括汉字、数字、字母、空格、下划线[)]匹配一个右括号中文的或者英文的)都算。把这三块拼起来就是从任意一个左括号开始跨越中间所有非括号内容到下一个任意右括号结束。整个匹配区间都会被替换成空字符串括号本身和括号里的内容一起消失。这条写法最爽的地方在于不需要转义括号。正则里直接用[(]而不是\(避开了字符串转义和正则转义叠加的坑尤其适合在SQL字符串里直接写。3.2 为什么不建议用.*去匹配括号内容很多人第一反应会写成\\(.*\\)这我当初也写过。问题出在.*是贪婪匹配它会尽可能匹配到最后面的右括号。举个活生生的例子字段是自然搜索(移动端)竞价(PC)用\\(.*\\)匹配它会从第一个左括号一直吃到最后一个右括号整个(移动端)竞价(PC)都被删掉结果只剩自然搜索用[(][^()]*[)]匹配它会先删掉(移动端)然后继续往后扫再删掉(PC)结果保留自然搜索竞价。为什么[^()]*能做到这种效果因为它限定了中间不能有任何括号字符一碰到下一个括号就停下来天然就是一种非贪婪行为。你不需要写.*?这种非贪婪量词而且有些数据库的正则引擎对非贪婪量词支持还不一致字符类写法反而更稳。3.3 嵌套括号要分轮处理写循环或递归CTE正则表达式解决不了任意深度的嵌套括号。比如苹果(山东(烟台))你跑一次上面的REGEXP_REPLACE结果是苹果(山东)。原因很简单第一次扫描时正则发现最外层左括号后面跟着另一个左括号[^()]*不允许中间出现括号于是它只能优先匹配到内层括号(烟台)删完之后外层括号还留在那。如果数据里嵌套最多两三层直接把REGEXP_REPLACE套两三次就行SELECT REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE(source_name, [(][^()]*[)], ), [(][^()]*[)], ), [(][^()]*[)], ) FROM channel_table;如果嵌套层数不确定在MySQL 8.0里可以用递归CTE每次递归都做一次替换直到字段里不再含有左括号为止WITH RECURSIVE clean AS ( SELECT id, source_name, 0 AS lvl FROM channel_table UNION ALL SELECT id, REGEXP_REPLACE(source_name, [(][^()]*[)], ), lvl 1 FROM clean WHERE source_name REGEXP [(] AND lvl 20 ) SELECT id, source_name FROM clean WHERE lvl (SELECT MAX(lvl) FROM clean c2 WHERE c2.id clean.id);这条递归CTE的逻辑是每一层替换一次直到不含左括号或达到20层的上限最后取每个id递归最深的那一层结果。它适合几万行以内的小表千万级的大表还是建议在应用层用脚本循环清洗别在SQL里硬递归。3.4 清洗完了顺手处理空格和NULL正则替换之后最容易出现的一个问题是残留空格。比如张三 (北京)括号前面有个空格删完括号内容后变成张三尾巴上多了个空格。所以实际使用时要套一个TRIMSELECT TRIM( REGEXP_REPLACE(source_name, [(][^()]*[)], ) ) AS cleaned_name FROM channel_table;如果字段值是NULLREGEXP_REPLACE也会返回NULL这多半不是问题。但如果下游逻辑要求不能有NULL记得在外面包一层COALESCE按需处理。4. 换到别的数据库怎么改PostgreSQL、Oracle、SQL Server、SQLite 的差异MySQL不是全世界中小团队最常碰到的其实是多种数据库并存。换引擎之后正则函数名、参数顺序、全局标志都会不一样我一个个说。4.1 PostgreSQL记住带g标志PostgreSQL的正则替换函数也叫REGEXP_REPLACE语法比MySQL多一个参数SELECT regexp_replace( source_name, [(][^()]*[)], , g ) AS cleaned_name FROM channel_table;最关键的是最后的g表示全局替换。如果漏掉gPostgreSQL默认只替换第一个匹配到的括号对字段里第二个括号对就原样保留。这一点和MySQL不一样MySQL的REGEXP_REPLACE默认就是全局替换。另外PostgreSQL的正则引擎是POSIX风格也和MySQL 8.0使用的ICU引擎在非贪婪量词等细节上有差异但我们用的[^()]*字符类写法在两边表现完全一致这也是我推荐这个写法的原因。4.2 Oracle正则函数最接近MySQL的写法Oracle 11g之后也提供了REGEXP_REPLACE用法和MySQL非常像SELECT REGEXP_REPLACE( source_name, [(][^()]*[)], ) AS cleaned_name FROM channel_table;Oracle的REGEXP_REPLACE默认替换所有匹配项所以不需要额外加参数。有一点要留意Oracle数据库如果字符集设置不当正则里的中文字符可能匹配不到全角括号。实际踩过这个坑之后我的建议是在Oracle里先把全角括号统一转成半角再跑正则SELECT REGEXP_REPLACE( REPLACE(REPLACE(source_name, , (), , )), [(][^()]*[)], ) AS cleaned_name FROM channel_table;把整个问题简化成纯半角括号能在很多奇怪的字符集环境下少一半麻烦。4.3 SQL Server没有正则时的 T-SQL 函数版SQL Server原生T-SQL到现在也没有正则替换函数很多人会立刻想到CLR但在生产环境里为了一个清洗需求启用CLR审批流程够你喝一壶的。简单场景用PATINDEX、CHARINDEX、STUFF组合做循环就能解决。CREATE FUNCTION dbo.RemoveBrackets(input NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE text NVARCHAR(MAX) input; DECLARE l INT, r INT, i INT, depth INT; WHILE 1 1 BEGIN SET l PATINDEX(%[(]%, text); IF l 0 BREAK; -- 从第一个左括号开始按括号深度找到真正和它配对的右括号 SET r 0; SET i l; SET depth 0; WHILE i LEN(text) BEGIN IF SUBSTRING(text, i, 1) IN ((, ) SET depth depth 1; IF SUBSTRING(text, i, 1) IN (), ) BEGIN SET depth depth - 1; IF depth 0 BEGIN SET r i; BREAK; END END SET i i 1; END IF r 0 BREAK; SET text STUFF(text, l, r - l 1, ); END RETURN text; END调用就很方便SELECT dbo.RemoveBrackets(source_name) AS cleaned_name FROM channel_table;这个函数比网上很多版本健壮原因是它用了一个深度计数遇到左括号就加1遇到右括号就减1深度归零的那个右括号才是真正和第一个左括号配对的。所以苹果(山东(烟台))这种嵌套数据也能一层一层删干净。4.4 SQLite建议拉回到应用层处理SQLite官方安装包默认不提供正则替换函数你手头可能也没有权限加载扩展。如果只是临时处理一批数据我的建议很直接把数据导出来在Python里用re.sub(r[(][^()]*[)], , data)洗一遍再写回SQLite。没必要在SQLite的SQL语法里硬碰硬性价比太低。当然如果你已经加载了regexp扩展那写法跟PostgreSQL差不多加不加g取决于扩展实现可以先跑一条验证再批量用。5. 实战复盘五个让我返工的坑以及排查思路前面代码给了不少但如果不知道坑在哪代码越顺手越容易在数据上翻车。这五个案例都是我实际遇到过或帮别人排查过的按真实排查顺序整理出来。5.1 中文括号导致替换毫无反应现象跑了REGEXP_REPLACE(name, \\(.*\\), )SELECT出来字段纹丝不动。排查思路先确认是不是真的匹配到了。用WHERE name REGEXP \\(.*\\)过滤一下发现明明有数据再用SELECT HEX(SUBSTRING(name, LENGTH(name)/2, 1))看一眼字符编码才发现括号不是半角的(和)而是全角和。正则里只写英文括号当然匹配不到中文括号。修复方法把正则改成同时覆盖两种括号也就是前面说的[(][^()]*[)]。5.2 贪婪匹配把不该删的也删了现象字段自然搜索(移动端)竞价(PC)预期的结果是自然搜索竞价实际执行后结果是自然搜索中间的竞价连带被删了。排查思路把正则提取出来单测发现问题出在.*。.*会一直吃到最后面的右括号把(移动端)竞价(PC)整体当成一个匹配段删掉。修复方法把.*换成[^()]*让匹配段在遇到下一个括号时立刻停下这样每对括号独立匹配互不干扰。5.3 嵌套括号删完还有残留现象字段苹果(山东(烟台))执行一次替换后变成苹果(山东)再执行一次才变成苹果。排查思路这不是正则写错而是正则本身不支持任意深度嵌套。[^()]*不能跨越括号所以每次都优先匹配内层括号外层括号只能等下一轮。修复方法一是手动嵌套多次REGEXP_REPLACE二是用递归CTE自动循环。分批跑之前先统计一下嵌套最深有几层SELECT MAX(LENGTH(name) - LENGTH(REPLACE(name, (, ))) FROM t;心里有个数。5.4 UPDATE 之后才发现忘了过滤行现象原本只想清洗一小批脏数据结果UPDATE语句没加WHERE直接把整表扫描了一遍。正则写对了还好最多是扫描慢正则要是写错了整表全改错。排查思路养成习惯任何时候UPDATE之前先跑SELECT把正则结果预览一遍SELECT id, source_name, REGEXP_REPLACE(source_name, [(][^()]*[)], ) AS cleaned_name FROM channel_table WHERE source_name LIKE %(% OR source_name LIKE %% LIMIT 200;确认无误之后再把它改成UPDATE并且加上带括号过滤条件的WHEREUPDATE channel_table SET source_name REGEXP_REPLACE(source_name, [(][^()]*[)], ) WHERE source_name REGEXP [(];即使是清洗任务我也建议先CREATE TABLE channel_table_bak AS SELECT * FROM channel_table;或者放进一个事务里跑完先回滚检查没问题再提交。宁可多敲一条备份命令也别赌自己正则一次写对。5.5 清洗完的空格和NULL不要忽略现象替换后的字段看起来干净了但按名字精确匹配时总是带个空格或NULL导致匹配不上。排查思路SELECT CONCAT([, cleaned_name, ])就能看出首尾有没有空格。NULL则要看正则函数对NULL的返回行为用COALESCE处理。修复方法统一包一层TRIM(COALESCE(...))同时注意字段里的中文空格、不间断空格必要时用REPLACE(cleaned_name, , )把全角空格也去掉。数据清洗做到最后往往不是括号难删而是这些看不见的空格和空值让你精疲力尽。最后分享一个我自己的小习惯凡是清洗类的SQL我都强制自己先跑一次SELECT预览再改成UPDATE并且动手前先看一眼字段里有没有嵌套括号、有没有中文括号、有没有多余空格。这个流程看起来多花几分钟实际上每次都能帮我省掉至少一小时的返工时间。写SQL去掉括号和括号里的内容听着像三分钟的小需求真做起来还是值得认真对待的。