英文:
ILIKE Compare Strings
问题
我想要匹配一个干净的列表和用户输入的混乱数据。我想要能够对以下查询返回1:
SELECT CASE WHEN REGEXP_REPLACE('Domino''s', '[^a-zA-Z0-9 ]', '') ILIKE CONCAT(CONCAT('%',REGEXP_REPLACE('Domino''s Pizza Dublin', '[^a-zA-Z0-9 ]', '')),'%') THEN 1 ELSE 0 END compare
英文:
I'm looking to match a list against user input data. The list is clean, the user input is messy. I want to be able to return a 1 for the following query;
SELECT CASE WHEN REGEXP_REPLACE('Domino''s', '[^a-zA-Z0-9 ]', '') ILIKE CONCAT(CONCAT('%',REGEXP_REPLACE('Domino''s Pizza Dublin', '[^a-zA-Z0-9 ]', '')),'%') THEN 1 ELSE 0 END compare
In my setup 'Domino''s' and 'Domino''s Pizza Dublin' is part of a dynamic list and are not fixed.
Any suggestions on how to get the above to return 1 instead of 0 would be great.
答案1
得分: 1
这在我的端返回1。
英文:
This is returning 1 on my end.
SELECT
REGEXP_REPLACE('Domino''s', '[^a-zA-Z0-9 ]', '') AS string1_clean,
REGEXP_REPLACE('Domino''s Pizza Dublin', '[^a-zA-Z0-9 ]', '') AS string2_clean,
'%' || REGEXP_REPLACE('Domino''s Pizza Dublin', '[^a-zA-Z0-9 ]', '') || '%' AS string2_clean_wildcard,
CASE
WHEN REGEXP_REPLACE('Domino''s Pizza Dublin', '[^a-zA-Z0-9 ]', '') ILIKE '%' ||REGEXP_REPLACE('Domino''s', '[^a-zA-Z0-9 ]', '') || '%'
THEN 1
ELSE 0
END AS compare;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论