MapInfo Pro

Expand all | Collapse all

Search and Replace tool with wildcard

  • 1.  Search and Replace tool with wildcard

    Posted 01-09-2020 09:42
    First off, I apologize if this has been asked before, I could not find an option to search all the threads first.

    Is there a way to use a wildcard with the Search and Replace mbx tool in MapInfo?

    I have tried using scripts like I would in a simple select, such as Like "%abc%" and using the standard asterisks * but nothing seams to work.

    I need to search and replace just a small portion of some of the rows, and I cannot do a global update as the rest of the data in the cells are different.  I need a wildcard because the error in my data that I am trying to fix is inconsistent throughout.
    For example

    I have a city name of Springdale, but sometimes it is spelled Springdl, and sometimes it's Springdal etc.  So I would like a wildcard to search for anything with "spring" within the cell, so that I can replace it with "Sprindale"

    Thanks in advance

    ------------------------------
    Ryan Richter
    GIS Network Data Specialist
    COX COMMUNICATIONS
    ------------------------------


  • 2.  RE: Search and Replace tool with wildcard

    Posted 01-10-2020 05:33
    Ryan,

    you can do this job in 2 parts:

    1) SQL to select records using wildcards as per your example.

    2) update appropriate column for query1 result to change all these records to "Sprindale"

    Regards

    Nick




    ------------------------------
    Nick Hall
    Mapchester LTD
    nick.hall@mapchester.co.uk
    ------------------------------



  • 3.  RE: Search and Replace tool with wildcard

    Posted 01-10-2020 08:25
    And if you want a single statement, you can do so with MapInfo Pro v2019:

    Update CITIES Set NAME = "Springdale" Where NAME Like "%Spring%"

    where CITIES is the name of the table and NAME is the column with the city names

    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------



  • 4.  RE: Search and Replace tool with wildcard

    Posted 01-10-2020 09:47
    Thank you both, but unfortunately, this will not work in my situation, because the city name is not the only data within the cell.  I also have other data in the cell that I do not want updated or changed, which will happen if I do an update columns.  The cell is a list of fiber routes and the routes have an ID number, but we also incorporate the city name into the route name.  If it was just updating the city and that's all, then the update column would work fine.

    But in my instance, I need to search and select just the word with "spring" in it, so that I can replace them all with "springdale" and not touch or alter the rest of the data in the cell.

    I am guessing at this point, there is no wildcard function within search and replace like there is in excel.  So I think I will export it to excel and make the fix, and then using the unique ID column, I can update them that way.  I was just trying to see if there was a way I could do it in MapInfo without having to go outside it and do it in Excel.

    Thank you for your suggestions :)


    ------------------------------
    Ryan Richter
    GIS Network Data Specialist
    COX COMMUNICATIONS ROANOKE
    ------------------------------



  • 5.  RE: Search and Replace tool with wildcard

    Posted 01-10-2020 10:07
    Hi Ryan

    How many different ways of spelling "Springdale" is there in your data? Maybe it's easier just to search and replace these if there isn't more than two handfuls?

    Alternatively, you could use some of the string functions in Mapbasic to "find" and "replace" the misspelled words. But at some point, it might be easier to just do it in Excel. It depends on how much other data you have in the column. If it's only the ID and the city, it might be quite easy. Even easier, if the ID always is of the same length.

    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------



  • 6.  RE: Search and Replace tool with wildcard

    Posted 01-13-2020 15:02
    Thanks for the options, I did end up just going through and making the changes manually.  I'm not sure how big of an issue it would be to put a wild-card option within the search and replace tool, and I'm sure that it's probably not in high demand either, but in this particular instance, it would have been great to have :)

    Thanks again!

    ------------------------------
    Ryan Richter
    GIS Network Data Specialist
    COX COMMUNICATIONS ROANOKE
    ------------------------------



  • 7.  RE: Search and Replace tool with wildcard

    Pitney Bowes
    Posted 01-13-2020 19:04
      |   view attached
    Hi Ryan,

    I have attached the source to a function extensions add-in which includes a regular expression search and replace mapbasic function that would have helped you.  For now you would have to build it with mapbasic and .net, but it might be worth it to clean it up and add it to the MapInfo Marketplace.

    Once loaded, it exposes a function that you could use like this (the actual search and replace expression you would have to verify):
    Update CITIES Set NAME = RegEx$(NAME, "\b(\w+?)\s\b", "Springdale")

    If you need help building this let us know.

    -Bob

    ------------------------------
    Bob Fortin
    Software Architect and Distinguished Engineer
    MapInfo Pro Development Team
    ------------------------------

    Attachment(s)

    zip
    FunctionExtensions.zip   9K 1 version