ILIKE比较字符串

huangapple go评论53阅读模式
英文:

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;

huangapple
  • 本文由 发表于 2023年4月13日 20:15:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/76005309.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定