Hi MI-Lers
Thankyou to Jacques Paris, Martin Spiers, Jose Luis Armero, Photogrammetry GUI,
Thomas Brix lyng, Greg Driver, Rob Davis and Julian Parker for the
comprehensive volume of replies to my question!
The suggestion by many of you to use the following expression:
Update Mytable Set MyCol = mid$( MyCol, instr(1, MyCol, " ")+1,
len(MyCol))
worked for me. The logic behind it follows:
The combination of the functions Mid$(), Len() and Instr() will help
solve your problem.
For example given
Mytxt = "2105 ALBANY CREEK"
the statement
Mid$( Mytxt , Instr(1, Mytxt , " ") +1, Len(Mytxt))
will return
"ALBANY CREEK".
Instr(1, Mytxt , " ") returns the position of " " in Mytxt starting
with the 1st character.
Len(Mytxt) returns the number of characters in Mytxt.
Mid$( Mytxt , Instr(1, Mytxt , " ") +1, Len(Mytxt)) returns the range
of characters defined by the other 2 functions.
The following statement will then replace the contents of the table.
Update Mytable Set MyCol = mid$( MyCol, instr(1, MyCol, " ")+1,
len(MyCol))
Note: I saved this table prior to using it in the join operation.
Also suggested were:
If a is the name of the column containing the address then update a column
with
right$(a, len(a)-instr(1,a," "))
That assumes that all addresses start with a number. You could check that by
making a selection where : val(left$(a,1)) = 0. If any are found, check them
before running the above update
and
I think you can resolve your problem finding the first appearance of the space
carácter : " ".If you don't have MapBasic, you can do the following.Create a
new column, name it Space for example, and update it with the value:
Instr(1,locality," ") This function writes the number of characters from left
to the first space.Then, update your locality field with:
Mid$(locality,Space+1,len(locality)-space) and
Try update column: YourColumn=Right$(YourColumn,Len(YourColumn)-5)
Ofcourse YourColumn always have to start with "#### " for this to work.
Else try this:
YourColumn=Right$(YourColumn,Len(YourColumn)-InStr(1,YourColumn," "))
In this way the function deletes all the chars until and with the first
space.
and
If you exported the data to Excel you could use a formula like
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
where cell A1 contains your current identifier (2105 Albany Creek). This
formula locates the first blank and then copies the text to the right of
that.
The resultant table could then be brought back into Mapinfo and used to
update the original table, if required.
and
If you can't get MapInfo/MapBasic to delete the numerics, then try exporting
the table into something like Access or Excel - I usually use delimited text.
Once in Access, for instance, you can use 'find and replace' for each numeric,
10 search/replaces, to produce blanks or spaces. Then you can use 'trim' to
get rid of the leading spaces. Then adding the table back in MapInfo and
joining to your original file you can transfer the amended field into your
original table.
Bit messy, I know, but it should work if all other things fail.
In any case, this all goes to show there is more than one way to skin a
cat(alogue).
Thankyou again,
Alistair.
[EMAIL PROTECTED]
Hi
I have a column of data with a numeric code preceeding the locality name I'd
like to join records with.
I have searched the archives but haven't been able to find a reference to a
mapbasic command that would update the column to remove the numeric data.
ie: 2105 ALBANY CREEK
and I want 'ALBANY CREEK'
It should be easy as there is a space after the numberic data but the numeric
data varies in characters from 1-4 long so i can't just trim the first five
characters from the field values.
I cannot attack from the right hand side because the number of words (and
subsequently spaces) between the right side and the numbers varies.
Is there a way to join records where tableX.locality string is within
tableY.locality string? Or is there a bit of MB code I should use?
I've even taken the desparate step of reading the MI Pro UserGuide, to no
avail...
I'm using V7.0 and 7.8.
Cheers,
Alistair.
***************************************************
Alistair Hart
GIS Project Officer
Health Surveillance
Tropical Public Health Unit Network
PO Box 1103
Cairns QLD 4870
Ph: 0740 503 628
Fax: 0740 311 440
E: [EMAIL PROTECTED]
"If our hopes of building a better
and safer world are to become more
than wishful thinking, we will need
the engagement of volunteers more
than ever."
Kofi Annan, Secretary General,
United Nations
***********************************************************************************
This email, including any attachments sent with it, is confidential and for the
sole use of the intended recipient(s). This confidentiality is not waived or
lost, if you receive it and you are not the intended recipient(s), or if it is
transmitted/received in error.
Any unauthorised use, alteration, disclosure, distribution or review of this
email is prohibited. It may be subject to a statutory duty of confidentiality
if it relates to health service matters.
If you are not the intended recipient(s), or if you have received this email in
error, you are asked to immediately notify the sender by telephone or by return
email. You should also delete this email and destroy any hard copies produced.
***********************************************************************************
***************************************************
Alistair Hart
GIS Project Officer
Health Surveillance
Tropical Public Health Unit Network
PO Box 1103
Cairns QLD 4870
Ph: 0740 503 628
Fax: 0740 311 440
E: [EMAIL PROTECTED]
"If our hopes of building a better
and safer world are to become more
than wishful thinking, we will need
the engagement of volunteers more
than ever."
Kofi Annan, Secretary General,
United Nations
***********************************************************************************
This email, including any attachments sent with it, is confidential and for the
sole use of the intended recipient(s). This confidentiality is not waived or
lost, if you receive it and you are not the intended recipient(s), or if it is
transmitted/received in error.
Any unauthorised use, alteration, disclosure, distribution or review of this
email is prohibited. It may be subject to a statutory duty of confidentiality
if it relates to health service matters.
If you are not the intended recipient(s), or if you have received this email in
error, you are asked to immediately notify the sender by telephone or by return
email. You should also delete this email and destroy any hard copies produced.
***********************************************************************************
