SQL LIKE 查询:转义通配符 [英] SQL LIKE query: escape wildcards

查看:96
本文介绍了SQL LIKE 查询:转义通配符的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个 LIKE 查询,我在 Android Uri 的列上运行,其格式如下:

  1. content://com.android.externalstorage.documents/tree/0000-0000%3A_Issues/document/0000-0000%3A_Issues%2FParentB
  2. content://com.android.externalstorage.documents/tree/0000-0000%3A_Issues/document/0000-0000%3A_Issues%2FParentA
  3. 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%';

  1. 如果我转义%":

<块引用>

%_Issues\%2FParentB%'

我根本没有得到任何结果.

  1. 如果我不逃脱:

<块引用>

'%_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:

  1. content://com.android.externalstorage.documents/tree/0000-0000%3A_Issues/document/0000-0000%3A_Issues%2FParentB
  2. content://com.android.externalstorage.documents/tree/0000-0000%3A_Issues/document/0000-0000%3A_Issues%2FParentA
  3. 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%';

  1. If I escape '%':

%_Issues\%2FParentB%'

I get no results at all.

  1. 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆