0

In SQL (let's assume ANSI standard SQL), is it possible to extract a substring from one column, based on another table, and put this substring into another column?

Example, from the following tables:

VISITS
name       summary
---------------------------------------------
john       visit to London
jack       hotel in Paris with park visits
joe        b&b in Paris with ice cream
james      holidays in London and museums
jason      visit in Milan and fashion tour

LOCATIONS
id    name
-------------
1     Paris
2     London
3     Milan
4     Berlin

The idea is to extract the location from the summary column and output the following:

VISITS
name       summary                              location
---------------------------------------------
john       visit to London                      London
jack       hotel in Paris with park visits      Paris
joe        b&b in Paris with ice cream          Paris
james      holidays in London and museums       London
jason      visit in Milan and fashion tour      Milan
Jivan
  • 21,522
  • 15
  • 80
  • 131
  • Please tag your question with the database that you are using. – GMB Mar 27 '20 at 15:03
  • @GMB I usually do it but in this case I'm not sure — I'm using the Redash platform and not sure what standard they're using – Jivan Mar 27 '20 at 15:05

1 Answers1

1

You can do pattern matching:

select v.*, l.name
from visits v
left join locations l on v.summary like concat('%', l.name, '%')

As commented by jarlh ANSI sql has operator || for string concatenation, so, depending on your database:

select v.*, l.name
from visits v
left join locations l on v.summary like '%' || l.name || '%'
jarlh
  • 42,561
  • 8
  • 45
  • 63
GMB
  • 216,147
  • 25
  • 84
  • 135