0

In an Android application,
In SQLite database, there is a column with the following data:
A~B~C~D~E~F
Where each of A,B,C,D,E and F could have variable length and data

For example,
A could be every possible interested name like Mike, Andy, Tom , ...
Or B could be any interested country name and so on

Now I need a Query to reach to D part, how?
FYI, '~' character is unique in the data (there would be just 5 characters of '~')
In other word, A,B,C,D,E,F don't contain '~'

Edit: I need LIKE clause in a basic SQLite

AndroidPlayer
  • 71
  • 6
  • Does this answer your question? [Split a string into rows using pure SQLite](https://stackoverflow.com/questions/34659643/split-a-string-into-rows-using-pure-sqlite) – Marged Jan 14 '20 at 06:04
  • 1
    https://stackoverflow.com/a/32051164/2383176 – javadroid Jan 14 '20 at 06:21
  • @Marged,Sorry, I don't think so, I need a LIKE clause for basic SQLite to handle such an issue – AndroidPlayer Jan 14 '20 at 06:22
  • 1
    Don't try to store multiple things in a single column. Each of those values should be in a row of its own in a table. – Shawn Jan 14 '20 at 07:42
  • @Shawn, I tested it and understood the size of database is considerably bigger if every item is separately placed in its own column, I don't know why but it happens – AndroidPlayer Jan 14 '20 at 08:25
  • The linked answer shows the concepts you need to use, have a closer look or look at what javadroid linked – Marged Jan 14 '20 at 22:49
  • No, please take a look at my own answer, thanks – AndroidPlayer Jan 15 '20 at 09:06

2 Answers2

1

I actually don't know of a way to do this using only the basic SQLite string functions. Therefore, I would suggest handling this from Java, after you have queried:

String input = "A~B~C~D~E~F";
String[] parts = input.split("~");
String target = parts.length >= 4 ? parts[3] : "";
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

Eureka! It's possible with LIKE clause (NOT LIKE) in a basic SQLite: 

          SELECT_QUERY =
                        String.format("SELECT * FROM %s WHERE %s NOT LIKE %s;",
                                Constants.TABLE_M4,
                                Constants.KEY_M4_Data,
                                "%~%~%~~%'"
                        );

If D is not empty, it is identified with the above query

AndroidPlayer
  • 71
  • 6