Hi people
i am having some difficulty with a quite advanced query (at least from my level).
select * from cities join areacodes on contains (city,'select area from areacodes right join cities on area not in (select city from cities)' )
Server: Msg 7631, Level 15, State 1, Line 1
Syntax error occurred near 'area'. Expected ''''' in search condition 'select area from areacodes right join cities on area not in (select city from cities)'.
what i am trying to do is find the areas in the postcodes table that contain words that are in the cities table. I need to match them up and modify the data in the postcodes table. the data is inconsistent as it comes from two unrelated sources. i have to be aware of spelling mistakes etc and have tried usind the soundex function to match them but it produces too many results. as you can see from the data in the postcodes table some areas (the second column) are not entirely upper case. these are the areas that have matched records in the cities table and the data is valid.
please can someone suggest the correct and easy way of doing this?
Thanks
Chris Morton
POSTCODES TABLE FIRST 30 ROWS
CITIES TABLE FIRST 30 ROWS
Hi Chris,
The only way I can think of that you can leverage CONTAINS/fulltext functionality in this case is unfortunately through a cursor - you'll need to fetch a row from the Cities table and run a CONTAINS query on your Postcodes table per every city you fetch.
If you have a requiement for matching large amounts of "fuzzy" data, you may want to check if Fuzzy Lookup Transform in SSIS could be useful.
Hope this helps.
Best regards,
No comments:
Post a Comment