how to use variable values in sqlite string pattern match?

Question

is it possible to use variables in LIKE pattern matches in sqlite queries? i have the tables:

Table 1:

id  names  linker  
1   williams, john; jones, lisa   A1
2   jones, lisa                   A1
3   sanders, karim                A2
...

Table 2:

index  name      linker
X      williams  A1
Y      jones     A1
Z      sanders   A2

i want to join Table 1 to Table 2 on the linker column, grouped by Table2.index, but only consider entries from Table 1 where Table1.name is a substring of Table2.names, ie Table2.names LIKE '%Table1.name%'. in above tables williams entry with id 1 in table1 would be associated with index X in table2, but entry with id 2 of table1 would not. how can a column be used inside a pattern match? this does not work

select * from table1 join table2 on (table1.linker == table2.linker) where names like '%' + name + '%' group by table1.index

CL. · Accepted Answer · 2017-04-28 10:42:42Z

2

In SQL, + is used to add numbers. To concatenate strings, use ||:

SELECT *
FROM table1
JOIN table2 USING (linker)
WHERE names LIKE '%' || name || '%'
GROUP BY table1.id;

answered Apr 28, 2017 at 10:42

CL.

182k18 gold badges241 silver badges282 bronze badges

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

Collectives™ on Stack Overflow

how to use variable values in sqlite string pattern match?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related