Match query fror comma separated string in sqllite?

Question

Which is better way to find particular number from comma separate string in sqlite ?

my datatable

------------------------------------------------------------------------- 
|id                 |   name                 |  relations(This is String)|
-------------------------------------------------------------------------
|1                  |   ABC                  |  1,2,3,4,5                |
-------------------------------------------------------------------------
|2                  |   XYZ                  |  14,3,4,5                  |  
-------------------------------------------------------------------------

My goal is to find row which relation contains 1 and delete this relation from string but when i use like query it returns me both rows to and rest of things i do in java

I want to find exact rows which have exact match i know it can be done using regexp i dont know how

Step 1 : execute this query "SELECT relations FROM table_name" step 2 : separate values using "split" function Step 3 : execute your delete query — Amsheer, May 23 '14 at 06:47
The best way to handle this is to change the database structure so that you have a second table for the relations, with one value per row. — CL., May 23 '14 at 07:04

score 0 · Answer 1 · edited May 23 '17 at 12:08

You cannot search for regex in sqlite database in android. See this how to search sqlite database using regular expression in android?

But you can search for the regex in the string that is returned:

 String RESULT_FROM_DB = "1111,1,3,234,1,2,4";
 Pattern p = Pattern.compile("\\b1\\b");
 Matcher m = p.matcher(RESULT_FROM_DB);
 if(m.find()){
     //Do your stuff here. 
     System.out.println(m.start());
     System.out.println(m.end());
 }

You should replace 1 in the regex used to compile with the number that you need (Dynamic value as you said.)

Match query fror comma separated string in sqllite?

1 Answers1