SQL子查询结果含多余括号，如何修正得到目标输出？

问题场景 #

我在拼接SQL查询结果时，子查询返回的用户名包含多余括号，导致输出不符合预期。

第一段SQL及问题

原SQL片段：

SUBSTR(work__note.note_text,1,10000) || '  ' ||  (select user_name from cm_use where SEQ_USER_ID in work_request_note.create_user_id) || '   ' || work__note.create_date   "Notes"

• 当前输出："Work was assigned to Kristie Cut (ard1) 01-NOV-23"
• 期望输出："Work was assigned to Kristie Cut ard1 01-NOV-23"

第二段SQL及问题

原SQL片段：

SUBSTR(work_note.note_text,1,10000) || '  ' ||  (select user_name from cm_use where SEQ_USER_ID in work__note.create_user_id) || '   ' || work_note.create_date   "Notes"

• 当前输出："Work Request was assigned to Kri Cut (kld1) 01-NOV-23"
• 期望输出："Work Request was assigned to Kri Cut kld1 01-NOV-23"

解决方案 #

问题根源是cm_use表的user_name字段存储格式为姓名 (标识)，只需用正则替换函数去除括号，将标识直接拼接在姓名后即可。以下是修改后的SQL：

修改后的第一段SQL

SUBSTR(work__note.note_text,1,10000) || '  ' ||  
REGEXP_REPLACE((select user_name from cm_use where SEQ_USER_ID = work_request_note.create_user_id), '\s*\((.*?)\)', ' \1') 
|| '   ' || work__note.create_date   "Notes"

修改后的第二段SQL

SUBSTR(work_note.note_text,1,10000) || '  ' ||  
REGEXP_REPLACE((select user_name from cm_use where SEQ_USER_ID = work__note.create_user_id), '\s*\((.*?)\)', ' \1') 
|| '   ' || work_note.create_date   "Notes"

说明 #

1. 正则替换逻辑：
   • \s*\((.*?)\)：匹配括号及括号前后的任意空白字符，(.*?)捕获括号内的标识内容
   • ' \1'：将匹配到的括号部分替换为一个空格加捕获到的标识，实现去除括号并保留标识的效果
2. 优化点：原SQL中SEQ_USER_ID in work_request_note.create_user_id若create_user_id为单个值，改用=更高效，避免不必要的集合判断。

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