Why LEFT JOIN on LIKE statement gives different result on "

table1:

code	category
ABC	ZMLND_XY_ABC

table2:

category	label
MLND	3
ZMLND	4

I'd like to map table1's category to table2's label if the part of table1's category separated by _ equals table2's category. For example, ZMLND_XY_ABC in table1 matches ZMLND in table2 while ZMLND_XY_ABC and MLND does not.

Query:

select * from table1 left join table2 on '_'||table1.category||'_' like '%_'||table2.category||'_%'

Result:

code  |    category     |  category_1  |  label
ABC   |  ZMLND_XY_ABC   |    MLND      |    3      <-  _MLND_ should not match
                                                       _ZMLND_XY_ABC_
ABC   |  ZMLND_XY_ABC   |    ZMLND     |    4

However, I replace _ by ,:

table1:

code	category
ABC	ZMLND,XY,ABC

select * from table1 left join table2 on ','||table1.category||',' like '%,'||table2.category||',%'

Result:

code	category	category_1	label
ABC	ZMLND,XY,ABC	ZMLND	4

Why are there different results?

CodePudding user response：

Because of SQLITE like the _ is as special a character as the %.
As you seem to know, the % matches multiple characteres.
What you seem to have missed is that the _ matches any single character.
Compare e.g. https://www.sqlitetutorial.net/sqlite-like/

Have a look at https://www.sqlite.org/lang_expr.html concerning the ESCAPE optional syntax part, which probably can help you to write your query.