MapInfo Pro

 View Only

  • 1.  Delete characters before a space

    Posted 03-24-2020 22:31
    Hi All,

    I have a list of roads eg. Smith Street.

    I want to remove all the characters before the blank and end up with the street type.

    I can use the update column command

    Right$(field_name, ????)

    But I can't work out the exact syntax.

    If there is already a thread on this please direct me to it.

    regards,
    Neil

    ------------------------------
    Neil Adamson
    GIS Officer
    Mitchell Shire Council
    Broadford VIC
    ------------------------------


  • 2.  RE: Delete characters before a space

    Posted 03-24-2020 22:54
    Hi Neil,

    First, select all records that contain a space:

    Select * From [table name] Where [field name] like "% %" Into updateTable
    Update updateTable Set [field name] = Mid$([field name], instr(1,[field name]," ")+1, len([field name])-instr(1,[field name]," ")+1)

    Then check the results in case you need to repeat to cater for "Mia Mia Road" for example.

    ------------------------------
    James Nolet
    Dooley Mitchell & Morrison Pty Ltd
    Mentone, VIC, Australia
    ------------------------------

    Original Message


  • 3.  RE: Delete characters before a space

    Posted 03-25-2020 11:35
    I had to work on a project similar to this a couple years ago and parse all of my data. The main thing to do before using the update column command is to create a new temporary column where you want the parsed data to go. For instance, you want the street type, so name the new temp column "street_type_temp" with the type character width at 60. When you use the update column tool be sure to :

    Table to update: whatever the name of your table is
    Column to update: name of temp column you created
    Get value from table: same as the table to update
    Value: select assist and copy and paste one of the formulas below and select verify to make sure the syntax is correct.

    You can update your new column ("street_type_temp")with everything after the 1st space like this:
    Mid$(Roads, Instr(1,Roads," ")+1,999)
    Replace the red text with the name of the column where your roads data is at.

    If you want everything before the first space use this:
    Left$(temp column name, Instr(1,temp column name," ")-1)

    Here are a few more that you can play with, but I don't remember exactly what they do.

    Mid$(temp column name, Instr(1,temp column name," ")+1,999).


    Left$(temp column name, Instr(1,temp column name," ")-1)

    Left$(Address, Instr(1,Address," ")-1)
    Replace the red text with the name of the column where you want to pull data from



    I hope this helps!

    ------------------------------
    Rachel Taylor
    GIS Analyst
    ------------------------------

    Original Message


  • 4.  RE: Delete characters before a space

    Posted 03-25-2020 17:21
    Hi Neil,

    ​My method is to use the following syntax in Update Column:

    Right$(field, Len(field) -InStr(1, field, " "))

    Re-run for multi-word names eg. Mia Mia Road if necessary.

    Cheers,

    ------------------------------
    David Murphy
    GIS Officer
    Swan Hill Rural City Council
    Swan Hill VIC
    ------------------------------

    Original Message