当使用SQL（Teradata）模糊搜索多个单词时，在结果中，如何列出哪个单词匹配？

I want to use like to query some records like:

我想使用像查询一些记录，如：

select table1.survey_txt where table1.survey_txt like any (
'%aaaa%',
'%bbbb%',
'%cccc%',
'%dddd%',
'%eeee%',
'%[^a-z0-9]ffff[^a-z0-9]%'
)

The result will be like:

结果如下：

survey_txt

survey_txt

heheaaaahehe

kekeaaaakeke

uueabbbbk

jioewccccfjo

esjoffffownr

So how can I make the result like:

那么如何才能得到如下结果：

survey_txt..........matched_word

survey_txt .......... matched_word

heheaaaahehe........aaaa

heheaaaahehe ........ AAAA

kekeaaaakeke.........aaaa

kekeaaaakeke ......... AAAA

uueabbbbk...............bbbb

uueabbbbk ............... BBBB

jioewccccfjo..............cccc

jioewccccfjo .............. CCCC

esjoffffownr..................ffff

esjoffffownr .................. FFFF

This is a table but I don't know how to make a table here so I just used "....." to make it look like table.

这是一张桌子，但我不知道如何在这里制作一张桌子所以我只是用“.....”使它看起来像桌子。

2 个解决方案

#1

Use REGEXP_SUBSTR instead of LIKE:

使用REGEXP_SUBSTR而不是LIKE：

SELECT survey_txt,
   RegExp_Substr(survey_txt, '(aaaa|bbbb|cccc|dddd|eeee|[^a-z0-9]ffff[^a-z0-9])') AS matched_word
FROM surveys
WHERE matched_word IS NOT NULL

#2

Try building a table with the search words and like strings, then inner joining. Since you are using Teradata something like this example should work:

尝试使用搜索词和类似字符串构建表，然后内部连接。既然你正在使用Teradata这样的例子应该有效：

CREATE VOLATILE TABLE searchwordslist
  (searchword VARCHAR(32)
  , likestring VARCHAR(32))
ON COMMIT PRESERVE ROWS
;
INSERT INTO searchwordslist VALUES('aaaa', '%aaaa%');
INSERT INTO searchwordslist VALUES('bbbb', '%bbbb%'); 
--and so on for all the words

So now our searchwordlist table looks like:

所以现在我们的searchwordlist表看起来像：

searchword  likestring
bbbb        %bbbb%
aaaa        %aaaa%

Then your query is:

然后你的查询是：

SELECT a.survey_txt 
  , b.searchword
FROM table1 a
INNER JOIN searchwordslist b
  ON a.survey_txt LIKE b.likestring
;

Giving your desired result:

给出你想要的结果：

survey_txt      searchword
heheaaaahehehe  aaaa
kekebbbbkeke    bbbb

#1