I am trying to make the search function in mashup more versatile by creating a new column in my SQLite places table called full_location where it will hold the postal_code, place_name, and admin_name1 as a concatenated string. I have written a program to create a txt file with all the concatenated locations. I am wondering if there's a way to import from a txt file to a specific column in my places table. Kind of like what we did for US.txt, but with a specific column in mind.
1 Answer
You could do something like: make the text file have 2 columns, postal_code and full_location. Then import that file into a temp table. Then write a sql to update the places table with full_location from the temp table matching on postal_code.
Another approach would be to use sql and the concatenation operator to simply update the column in sql. The concatenation operator is ||
(pipe-pipe). Something like:
update places
set full_location = postal_code || ' ' || place_name || ' ' || admin_name1
From the sqlite doc:
The
||
operator is "concatenate" - it joins together the two strings of its operands.
-
Thank you so much. That worked! Unfortunately, when I search through SQL using ("SELECT * FROM places WHERE full_location LIKE :q", q=q) only the postal_code works. Sep 4, 2017 at 19:43
-