Match substring in string SQLite

Question

So i have two table that looks like this:

Table 1

IP        | Product | Version
192.x.x.x | IBM BD2 | 9.7

Table 2 (A string, that is somewhat a concat of Product and Version)

ID  |  Platform                 | Description
50  |  IBM DB2 9.7 < FP11 40690 | This is XYZ

I want to be able to search Table1.Product and Table1.Version against Table2.Platform and return something like the following.

Table 3

 Product  | Version | Description
 IBM DB2  | 9.7     | This is XYZ

I have tried using IS LIKE and INSTR but these don't seem to work.

Im not sure if its possible as the two tables have no relationship and the matches won't be exact, I'm looking for a sort of "Find in String" to isolate the product and the version out of the platform string.

My current query is:

   SELECT Table1.Product Table1.Version Table2.Description FROM Table2, Table1 WHERE Table1.product IS LIKE Table2.platform


Error: near "Table2": Syntax Error

"IS LIKE" is not correct sqlite syntax. The operator is "LIKE". — DinoCoderSaurus
– DinoCoderSaurus, Commented Mar 26, 2021 at 11:05

forpas · Accepted Answer · 2021-03-26 11:06:23Z

1

For this sample data you need a join of the tables and the operator LIKE in the ON clause:

SELECT t1.Product, t1.Version, t2.Description 
FROM Table1 t1 INNER JOIN Table2 t2 
ON t2.Platform LIKE t1.Product || ' ' || t1.Version || '%'

See the demo.

answered Mar 26, 2021 at 11:06

forpas

165k10 gold badges51 silver badges85 bronze badges

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

2 Comments

Verudux Over a year ago

Could you clarify what the || ' ' || t1... stuff is. quite confused about that. Thank you though

forpas Over a year ago

|| is the concatenation operator of SQLite, so I concatenate Product and version to compare it against Platform.

Maira Teixeira de Melo · Accepted Answer · 2021-03-26 11:03:39Z

0

Check if this works for you:

db2 "SELECT Table1.Product, Table1.Version, Table2.Description FROM Table2, Table1 where CONCAT(CONCAT(Table1.Product, ' '), Table1.Version) like (select SUBSTR(Platform, 1, 11) from Table2) "

answered Mar 26, 2021 at 11:03

Maira Teixeira de Melo

13 bronze badges

Collectives™ on Stack Overflow

Match substring in string SQLite

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related