From this "INVESTMENT" to this "INVST"

Rick Schreiber · #1 (**permalink**) 07-22-04, 14:16

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

lmckelvy · #2 (**permalink**) 07-22-04, 15:52

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.

Shores · #3 (**permalink**) 07-22-04, 18:05

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.

Rick Schreiber · #4 (**permalink**) 07-22-04, 22:38

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

Shores · #5 (**permalink**) 08-07-04, 14:55

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

sundialsvcs · #6 (**permalink**) 09-24-04, 23:11

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.

Maroonotmoron · #7 (**permalink**) 09-28-04, 16:52

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

Internet Services

Web Development

Digital Marketing

Consumer Tech