SQL LIKE 查询:转义通配符 [英] SQL LIKE query: escape wildcards
问题描述
我有一个 LIKE 查询,我在 Android Uri
的列上运行,其格式如下:
- content://com.android.externalstorage.documents/tree/0000-0000%3A_Issues/document/0000-0000%3A_Issues%2FParentB立>
- content://com.android.externalstorage.documents/tree/0000-0000%3A_Issues/document/0000-0000%3A_Issues%2FParentA立>
- content://com.android.externalstorage.documents/tree/0000-0000%3A_Issues/document/0000-0000%3A_Issues%2FParentA%2FParentB
这些对应于这个文件树:
_问题|-家长A|-父母B|-父母B
我尝试了各种手动查询来逃避通配符,但我似乎无法获得正确的匹配.为了测试,我将其简化为:
select name from meta where parent LIKE '%_Issues%2FParentB%';
- 如果我转义%":
<块引用>
%_Issues\%2FParentB%'
我根本没有得到任何结果.
- 如果我不逃脱:
<块引用>
'%_Issues%2FParentB%'
我同时匹配了浅层 ParentB(期望的)和嵌套的 ParentB(不期望的).我知道这是因为我允许在问题和 ParentB 之间使用 %.
我不明白的是为什么查询 1 没有结果.我错过了什么?
<小时>更新
select name from meta where parent LIKE '%_Issues_2FParentB%';
工作,注意'_',所以'\'显然没有逃脱这个数据库.明确说明转义字符@GordonLinoff 建议在我的电脑上工作:
select name from meta where parent LIKE '%_Issues\%2FParentB%' 转义 '\';
现在要弄清楚我们如何在 Android 中完成同样的事情...
是的.最简单的方法是使用 =
,如果合适的话.
否则,LIKE
语法包含转义字符.在您的示例中, $
不会出现在任何地方,因此您可以这样做:
where x like replace(y, '%', '$%') 转义 '$'
我相信在所有数据库中,默认都是\
,所以你也可以这样做:
where x like replace(y, '%', '\%')
但是,我更喜欢使用 escape
关键字,因此意图非常明确.
I have a LIKE query I'm running on a column of Android Uri
which are formatted like such:
- content://com.android.externalstorage.documents/tree/0000-0000%3A_Issues/document/0000-0000%3A_Issues%2FParentB
- content://com.android.externalstorage.documents/tree/0000-0000%3A_Issues/document/0000-0000%3A_Issues%2FParentA
- content://com.android.externalstorage.documents/tree/0000-0000%3A_Issues/document/0000-0000%3A_Issues%2FParentA%2FParentB
These correspond to this file tree:
_Issues
|-ParentA
|-ParentB
|-ParentB
I have attempted all sorts of manual queries escaping the wildcards, but I can't seem to get a proper match. I simplified it down to this for testing:
select name from meta where parent LIKE '%_Issues%2FParentB%';
- If I escape '%':
%_Issues\%2FParentB%'
I get no results at all.
- If I don't escape:
'%_Issues%2FParentB%'
I match both the shallow ParentB (desired) and the nested ParentB (undesired). I understand that's because I'm allowing anything between Issues and ParentB with the %.
What I don't understand is why query 1 does has no results. What am I missing?
Update
select name from meta where parent LIKE '%_Issues_2FParentB%';
worked, note the '_', so the '\' was clearly not escaping this db. Explicitly stating the escape character as @GordonLinoff suggested worked on my computer:
select name from meta where parent LIKE '%_Issues\%2FParentB%' escape '\';
Now to figure our how to accomplish the same in Android...
Yes. The simplest way is to use =
, if that is appropriate.
Otherwise, the LIKE
syntax includes an escape character. In your example, $
doesn't appear anywhere, so you could do:
where x like replace(y, '%', '$%') escape '$'
I believe that in all databases, the default is \
, so you could also do:
where x like replace(y, '%', '\%')
However, I prefer using the escape
keyword so the intention is really clear.
这篇关于SQL LIKE 查询:转义通配符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!