INSTR（）函数，SQLite，让Android的？ [英] instr() function SQLITE for Android?

问题描述

我想要做的对我现有的SQLite数据库以下Android上这是一种内置像 colomns：ID ---规则---路径--- someotherdata

有一个规则，现在如包含文件名的一部分（或者只是一些琐碎的东西，如mypicture或也喜欢JPG文件类型）。现在我想要做的是，我想编写一个查询，这得到我的所有规则，其中包含一个inputstring的一部分。 我曾尝试下面的例子：字符串值=somepicturefilename.jpg

我的发言：

 SELECT DISTINCT * FROM+ TABLE_RULES +WHERE INSTR（'+价值+ KEY_ID +，规则）= 0 ORDER BY！DESC;
 

的声明是在Java这样的价值应该得到在声明中插入。

不过，这是行不通的。我不是太熟悉SQL，也没有sqlite的，没有任何人有一个提示=;）谢谢

。

编辑：我也试着CHARINDEX，这也没擦出火花

。

编辑：让一个更详细的例子。 给出以下数据库。

  ID ---规则---
1JPG
2小马
3的gif
4PDF
 

现在用户输入文件名。比方说，poniesAsUnicorns.jpg。因此，poniesAsUnicorns.jpg是我的输入字符串和查询应与这两个ID＃1，ID＃2，因为poniesAsUnicorns.jpg包含JPG和小马

我希望澄清我想要什么。

编辑： 这里是我想太多：

 字符串声明=SELECT DISTINCT * FROM+ TABLE_RULES
            +WHERE CHARINDEX（规则'+价值
            +'）大于0 ORDER BY+ KEY_ID +DESC;;
 

但抛出一个没有这样的操作异常。

解决方案

AFAIK INSTR（）不可用，那么你可以使用：

  SELECT * from表名where代替（poniesAsUnicorns.jpg规则）poniesAsUnicorns.jpg=！;
 

或不区分大小写匹配：

  SELECT * from表名where替换（上（poniesAsUnicorns.jpg），上（规则），）=上！（poniesAsUnicorns.jpg）;
 

I wanna do the following on my existing sqlite database on android which is kind a built like that colomns: id --- rule --- path --- someotherdata

A rule now e.g. contains a part of a filename (either just some trivial stuff like "mypicture" or also a filetype like "jpg"). Now what I want to do is, I want to write a query, which gets my all rules, which contain a part of an inputstring. I have tried following example: String value = "somepicturefilename.jpg"

my statement:

"SELECT DISTINCT * FROM " + TABLE_RULES + " WHERE instr('"+ value + "',rule)!=0 ORDER BY " + KEY_ID+ " DESC;"

the statement is in java so the "value" should get inserted in the statement.

However, this does not work. I am not too familiar with sql nor sqlite, does anyone have a tip= ;) thanks.

edit: i've also tried charindex, which didn't work either.

edit: so a more detailed example. following database is given.

id --- rule ---
1      "jpg"
2     "ponies"
3     "gif"
4     "pdf"  

Now the user enters a filename. Let's say "poniesAsUnicorns.jpg". So "poniesAsUnicorns.jpg" is my input string and the query should match both id#1 and id#2 because "poniesAsUnicorns.jpg" contains both "jpg" and "ponies"

I hope that clarifies what I want.

edit: here is what i tried too:

String statement = "SELECT DISTINCT * FROM " + TABLE_RULES
            + " WHERE charindex(rule,'" + value
            + "') > 0 ORDER BY " + KEY_ID + " DESC;";

but throws a "no such operation" exception.

解决方案

AFAIK instr() is not available, so you can use:

select * from table where replace("poniesAsUnicorns.jpg", rule, "") != "poniesAsUnicorns.jpg"; 

or for case insensitive matches:

select * from table where replace(upper("poniesAsUnicorns.jpg"), upper(rule), "") != upper("poniesAsUnicorns.jpg");

这篇关于INSTR（）函数，SQLite，让Android的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！