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

Question

Assume I've the following table in sqlite database

Table name A with column A1 and A2

Table name B with column B1 and B2

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.

Tip of today: Switch to modern, explicit JOIN syntax! Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed! — jarlh
– jarlh, Commented Oct 20, 2020 at 11:49

Gordon Linoff · Accepted Answer · 2020-10-20 11:50:26Z

2

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).

answered Oct 20, 2020 at 11:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AvisSiva Over a year ago

Wow it works great. I tried string concatination using +, concat keyword, [ ] square brackets. nothing worked. Finally || this works. Thanks @Gordon

forpas · Accepted Answer · 2020-10-21 10:48:17Z

0

Maybe for this sample data a condition like:

A.A2 LIKE '%' || B.B2 || '%'

will work, but in general the condition should include the separator '/' of the items.

For example if you have a row in A with A2 equal to '\aa\bb\' then the above condition will return incorrect results if you search for 'a', because '\aa\bb\' LIKE '%a%' is TRUE.
You can check here how you will get wrong results.

So you must use this query:

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

See the demo.
Results:

> | A1 |
> | -: |
> |  1 |
> |  4 |

answered Oct 21, 2020 at 10:48

forpas

165k10 gold badges51 silver badges85 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related