How to join array of Strings? SQLITE

Question

I want to make a join between the table people and vehicles, which both share this value,

in the people table the field is vehicles

["https://swapi.dev/api/vehicles/14/","https://swapi.dev/api/vehicles/30/"]

and in the vehicles table the field is url

https://swapi.dev/api/vehicles/14/

As you can see in the first one there is like a list of strings, how could I do the query? is sqlite

I tried this, but does not work

SELECT *
FROM people
INNER JOIN vehicles
ON people.vehicles = vehicles.url

also i see this post How to join array of strings? but it didn't solve my problem

SQLite does not support arrays. What is the actual data in the vehicles column in the people table? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 23, 2021 at 5:11
@TimBiegeleisen this: ["https://swapi.dev/api/vehicles/14/","https://swapi.dev/api/vehicles/30/"] thats the data — Dubbain
– Dubbain, Commented Jul 23, 2021 at 15:40

Tashil Moorateeah · Accepted Answer · 2021-07-23 05:11:09Z

0

SELECT *
FROM people
INNER JOIN vehicles
ON people.vehicles like '%'+ vehicles.url  +'%'

answered Jul 23, 2021 at 5:11

Tashil Moorateeah

1167 bronze badges

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

2 Comments

Hi , that's return me nothing, does not work :/ please can keep me helping?

0 SELECT * FROM people INNER JOIN vehicles ON people.vehicles like CONCAT('%', vehicles.url ,'%')