在列中找到完全匹配时，如何在Oracle SQL中附加特殊字符 [英] How to append a special character in Oracle SQL when an exact match is found in the column

问题描述

表详细信息-具有2列ID和MSG_INFO

Table Details - Has 2 columns ID and MSG_INFO

ID - 1; MSG_INFO Party is carrying gold in a car which is made of gold

ID - 2; MSG_INFO Party is carrying whitegold in a car which made of gold

我想编写一个查询，如果我在MSG_INFO列中搜索黄金，并且有精确匹配，那么我将〜附加到黄金，如果没有完全匹配，则不执行任何操作.

I want to write a query in which if I search for gold in the MSG_INFO column and if there is an exact match then I append ~ to gold and no action to be performed when there is no exact match.

预期产量

ID -1 ; MSG_INFO Party is carrying ~gold in a car which is made of ~gold
ID -2 ; MSG_INFO Party is carrying whitegold in a car which made of ~gold

推荐答案

在Oracle REGEXP中，没有用于匹配单词边界的\b模式.常用的解决方案如下所示.

In Oracle REGEXP, there is no \b pattern to match word boundaries. A commonly used solution looks something like this.

SELECT id,
       REGEXP_REPLACE (msg_info, '(^|\s|\W)(gold)($|\s|\W)', '\1~\2\3', 1,0,'i')
FROM   yourtable;  

演示

这将在字符串的开头搜索单词gold，该单词在字符串的两边，结尾或非单词字符(例如?或-)周围被空格包围. \1,\2,\3表示在第一，第二和第三括号内匹配的字符.

This searches for the word gold at the start of the string, surrounded by a space on either side, end of string, or a non-word character (such as ? or -). \1,\2,\3 represent the characters matched within the 1st,2nd and 3rd parentheses.

这篇关于在列中找到完全匹配时，如何在Oracle SQL中附加特殊字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！