Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: From this "INVESTMENT" to this "INVST"

    Field contains [MAHONY INVESTMENT AND REAL ESTATE CO]

    I NEED TO CHANGE IT TO READ [MAHONY INVST AND REAL ESTATE CO]

    There will also be many instances where I need to change REAL ESTATE to REAL EST.

    I don't think a query can accomplish this. I have over 5,000 records in this database.

    Thanks. Rick

  2. #2
    Join Date
    Oct 2003
    Posts
    107
    This can be done with changeto queries.

    Code:
    | FieldName                                                          |
    | MAHONY INVESTMENT AND REAL ESTATE CO, changeto MAHONY INVST AND REAL ESTATE CO |

    Code:
    | FieldName                                  |
    | REAL ESTATE, changeto REAL EST. |
    Don't check any boxes. The table that appears on the desktop will be called Changed.db and contains a list of all original records changed by the query. The original table contins the modified records.

  3. #3
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    If you're trying to change the exact phrase, then the query that has been proposed to you is ok.

    If, instead, you need to change all apperances of "INVESTMENT" to "INVEST" regardless of where it appears in the field, then you should write a little script using Tcursors and Scan, since BDE doesn't have fulltext search capability.
    The only failure is not trying to do it.

  4. #4
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    TCursors Scan . . .

    Quote Originally Posted by Shores
    If you're trying to change the exact phrase, then the query that has been proposed to you is ok.

    If, instead, you need to change all apperances of "INVESTMENT" to "INVEST" regardless of where it appears in the field, then you should write a little script using Tcursors and Scan, since BDE doesn't have fulltext search capability.
    All appearances is correct!

    I am somewhat fimilar with TCursors and scan loops. But what identifier do I use, a string with the word "INVESTMENT" and then replace it with "INVST?" sometimes INVESTMENT will be the 4th word in the field, sometimes the 2nd word in the field.

    I have a little experience with Array[] but not that much.

    Any assistance would be greatly appreciated. The table has over 5,000 records and I have about 20 additional terms that need to be abbreviated.

    Tanks . . . Rick

  5. #5
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    Something like that would function:

    Code:
    method run(var eventInfo Event)
    var
    	tabletc tcursor
    	tablename string
    	fieldname string
    	wordsfrom array[] string
    	wordsto array[] string
    	tempstring string
    	counter smallint
    	sizefrom smallint
    endvar
    	tablename=":DAT:Table.db"
    	fieldname="FieldToChange"
    
    	wordsfrom[1]="INVESTMENT"
    	wordsto[1]="INVST."
    	wordsfrom[1]="INCORPORATED"
    	wordsto[1]="INC."
    
    
    	if tabletc.open(tablename) then
    		tabletc.edit()
    		scan tabletc:
    			tempstring=tabletc.(fieldname)
    			for counter from 1 to wordsfrom.size()
    				sizefrom=wordsfrom[counter].size()
    				foundpos=tempstring.search(wordsfrom[counter])
    				if foundpos>0 then
    					tempstring=tempstring.substr(1,foundpos-1)+wordsto[counter]+tempstring.substr((foundpos+sizefrom),(tempstring.size()-sizefrom-foundpos+1))
    				endif
    			endfor
    			tabletc.(fieldname)=tempstring
    		endscan
    		tabletc.endedit()
    		tabletc.close()
    	else
    		msgstop("Error","Cannot open the table")
    	endif
    endMethod
    The only failure is not trying to do it.

  6. #6
    Join Date
    Oct 2003
    Posts
    706
    One handy trick for jobs like this is to do a query to select all unique values for a column, making two copies of that column. Use the second column of the answer table to enter the replacement value. Then a CHANGETO linking to this "find this, change to that" table can edit all the value occurrences at once.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  7. #7
    Join Date
    Sep 2003
    Location
    Dallas
    Posts
    182

    Scan + match

    I would use the match function with the scan.
    tcursor.edit()
    Scan for tcursor.match(fieldname,".. Investment ..",a,b);
    fieldname = a + " Invst. " + b
    endscan


    The benefits of this is that it checks each record but only operates on those which have investment in them and replaces it immediately with Invst. Very precise and flawless method

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •