Search code examples
sqlstringsqliteandroid-sqlitesql-like

How to query in sqlite using like keyword with column name?

Assume I've the following table in sqlite database

Table name A with column A1 and A2

enter image description here

Table name B with column B1 and B2

enter image description here

I want to query like following

SELECT A1 FROM A, B WHERE A.A2 LIKE '%'+B.B2+'%' AND B.B1 = 1

I need the result of 2 rows which contains 1,4

But always it returns 0 rows.

Solution

  • The string concatenation operator in SQLite is ||. And you can express this as a JOIN:

    SELECT A1
FROM A JOIN
     B 
     ON A.A2 LIKE '%' || B.B2 || '%' 
WHERE B.B1 = 1;

    Note that || is the SQL standard string concatenation operator. + is overloaded in just a handful of databases owned by Microsoft (mostly).