0

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

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.

| improve this answer | |
  • 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. – Poliphria Sep 4 '17 at 19:43
  • You might try a % prefix, as well as suffix, on the q. – DinoCoderSaurus Sep 4 '17 at 20:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .