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
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 || '%'