Select first and last three strings from column for conditionSQL

Question

My goal is to select all the columns that start and end with the same 3 strings as the first row. In this case it was simple, since the CONCAT was equal to 'SCLMIA'

AND CONCAT(origin, destination) = 'SCLMIA' 
AND ((flight_path LIKE '%SCL%' AND flight_path LIKE '%MIA%')

but now the difficulty is for multiple strings.

AND CONCAT(origin, destination) IN ('SCLMIA', 'SCLIQQ','SCLMAD', 'LIMCUZ', 'BOGMDE', 'FORGRU', 'SDUCGH', 'SCLGRU', 'BOGLIM', 'GYEUIO')
AND (**here I need to replicate the same as above.**)

I read that it can be with the functions SUBSTRING, LEFT AND RIGHT selecting the three first and last strings but I don't know how to do it.

Tried with this, but failed: AND (flight_path LIKE '%' + SUBSTR(flight_path,3, LENGTH(flight_path) - 4) + '%')

It should be noted that it is a chain of conditions that's why start with AND.

Edit:

Image: Sample of data single path 'SCLMIA' It's from Bigquery.

Answer 1

I think this is what you're trying to do:

SELECT *
FROM 
    flight_paths
WHERE 
    CONCAT(origin, destination) IN ('SCLMIA', 'SCLIQQ', 'SCLMAD', 'LIMCUZ',  'BOGMDE', 'FORGRU', 'SDUCGH', 'SCLGRU', 'BOGLIM', 'GYEUIO')
    AND RIGHT(flight_path, 3) = origin 
    AND LEFT(flight_path, 3) = destination

Here's a db-fiddle that demonstrates the answer: https://www.db-fiddle.com/f/vUZ4HL4NC9xaBBZpwTYNcR/0