0

my problem is pretty much the same with this, but i'd like to do it using mysql.

find cities and country name from string

I have a table called cityList contain distinct city name in my country. When user insert an address, such as "12345 streetname st santa clara ca", i'd like to split between the address, city name, and state. "12345 streetname st", "santa clara", "ca" and insert it into another table.

i tried using substring_index to split all the word, and using cursor in stored procedure to check whether each of the text in the string is in cityList. i have problem with city that have than 1 word, such as "san diego" "santa clara" "rio de janeiro". Is there any better and simpler solution? thank you

user2771233
  • 21
  • 4
  • 1
    It'll be easier to get users to enter separate values for the different fields of an address – ack_inc Dec 24 '18 at 04:20
  • 1
    SQL is _not_ a fully featured programming language. You would be better off using some other language for this task. The link you gave shows only the tip of the iceberg. I would expect a robust routine to be hundreds of lines long in any language. – Rick James Dec 24 '18 at 05:11

0 Answers0