4

I would like to return the searched word including adjacent words which appear before and after the searched word using a query in SQLite. Is it possible to do this? Or should I attempt with another way?

E.g. for the below sentence (using the description column in the database table, column name is DESC):

> select DESC from TABLE_NAME where DESC like 'popular';

Which would return:

One of the most popular methods

But, I also want returned the two adjacent words "most" and "methods", like so:

most popular methods

I tried to check with REGEXP but the following are not working:

@"SELECT Desc FROM tablename WHERE LineText REGEXP '(^| )popular( |$)'"

@"SELECT LineText FROM table1 WHERE LineText REGEXP '[[:<:]popular[[:>:]'"

Can I get those with query or with other regular expression?

randomusername
  • 7,927
  • 23
  • 50
Niks
  • 647
  • 5
  • 16
  • What does this mean: "I want two adjacent words also "most" and "methods". from above text."? Also, are you using MySQL or SQLite? – Gordon Linoff Apr 03 '15 at 12:40
  • In MySQL, you can try `@"SELECT Desc FROM tablename WHERE LineText REGEXP '[[:<:]]popular[[:>:]]'"`. Also, you can check `@"SELECT Desc FROM tablename WHERE LineText LIKE '%popular%'"`. – Wiktor Stribiżew Apr 03 '15 at 12:42
  • @stribizhev, Yes I checked with this too earlier but it returns nil NSRegularExpression.. so may be anything wrong with syntax wise? not sure – Niks Apr 03 '15 at 12:44
  • @GordonLinoff This is the sample text in above question "One of the most popular methods". When user search for "popular" word I want "most" and "methods" words too. These both words appear in the sentence above. – Niks Apr 03 '15 at 12:48
  • In SQLite3, you can try using REGEXP like this: `@"SELECT Desc FROM tablename WHERE LineText REGEXP '\\bpopular\\b'"`. Does it work for you? – Wiktor Stribiżew Apr 03 '15 at 13:09
  • @stribizhev, Do apologize if I not explain properly, Yes this gives result but this gives all text of sentence. I want only required words as described in question. is it possible? – Niks Apr 03 '15 at 13:32
  • You need it for iOS, but I have only seen an example for Perl. In short, you need to implement your own function, like "capture", and then use it like `"SELECT CAPTURE('(\w+ popular \w+)', DESC) FROM TABLE_NAME"`. See example at http://www.ashleyit.com/blogs/brentashley/2013/11/27/using-regular-expressions-with-sqlite/. – Wiktor Stribiżew Apr 03 '15 at 14:18
  • @stribizhev Thank you very much. Let me check that how it can help me for iOS if I go with same logic. Thanks again. – Niks Apr 03 '15 at 14:22
  • If you manage to write your own implementation, please post as an answer. – Wiktor Stribiżew Apr 03 '15 at 14:24
  • @Niks I think you're going about this incorrectly. You should be able to get the returned string using your `LIKE` statement, and then use the returned string to parse out the words using your templating system, or output stream. – seangates Apr 15 '15 at 00:33

1 Answers1

0

I think you're going about this incorrectly. You should be able to get the returned string using your LIKE statement, and then use the returned string to parse out the words using your templating system, or output stream.

seangates
  • 1,467
  • 11
  • 28