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
heheaaaahehe
kekeaaaakeke
kekeaaaakeke
uueabbbbk
uueabbbbk
jioewccccfjo
jioewccccfjo
esjoffffownr
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
3
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
0
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
3
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
0
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