how to remove with changeto query? 1

[jlockley]

I am using Paradox to bring three phone books with many duplicates together. One of the programs has put a ' at the beginning of a number of records.

I would like to remove it with a changeto query.
',changeto "" isn't doing it. Where is my syntax amiss?
(The goal would be to have the name 'Jones appeaer as Jones.) Since I need to key for duplicates, I can't have a space or other character.

Gracious thanks to all. JLL

 

[BobRodes]

Although I've written maybe 10,000 lines of paradox code, mostly in the DOS versions, it has been quite a long time, about 10 years. Looks like your data are just about that old: Lotus users who didn't know how to delete cells properly would put a ' in a cell. Since that symbol was the left justify operator, it would appear to delete the cell, but in fact it kept the character in there. Created all sorts of havoc with all sorts of data exports.

As I recall, blank string is represented by the word blank in Paradox. So, try

', changeto blank

and see if that works.

HTH

Bob

 

[jlockley]

That was fast. I will let you know if two minutes. Thanks.
The data comes from a combination of Paradox 8, none that old, Palm and Cardscan files. It's kind of a mess.

 

[jlockley]

No effect. tried [']. "'" etc. Nope. Must be a way to do it.
Going to see if there is a workaround with ascii/word. Something hokey. It's driving me nuts.

 

[jlockley]

Thanks. No. No change. Also tried ["], "'"..using brackets and quotes for desired result..nada. Stumped. I appreciate the quick answer, though.

 

[TonyMcGuire]

You can't do this with a query.

Try tcursor and scanning. Just remember, if your table is keyed on the "fieldname" field, you'll need to remove the key prior to scanning and changing it.

tc.open(":alias:tablename.db")
tc.edit()
scan tc :
  if substr(tc."fieldname",1,1)="'") then
    tc."fieldname"=substr(tc."fieldname",2,tc."fieldname".size()-1)
  endif
  try
    tc.unlockrecord()
  onFail
; handle errors
  endTry
endscan
tc.endedit()
tc.close()

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[TonyMcGuire]

By the way, to select the records with the "'", I think you would do

"'.."

Problem is, you can't get the .. portion, or issue a substring.

You can with SQL, so you could go that route or try my SCAN routine, maybe.

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[BobRodes]

I should have mentioned that, Tony. I had the idea that jl was looking for only a ', but it did cross my mind that he might be looking for values that began with it. Sorry blank doesn't work.

Bob

 

[TonyMcGuire]

Blank works if you just want to eliminate the fields' values.

Problem is I think he wants to keep the rest of the info in the field and just remove the ' from the beginning.

SQL or scanning are the only ways I know of to do that.


Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[jlockley]

Works! Thanks. I will save this with the rest of the bag of tricks from this forum.

 

[BobRodes]

<Blank works if you just want to eliminate the fields' values.

Thanks Tony. Perhaps 5 years of Paradox programming experience has not fallen victim to age and neglect, after all.

Just so we know, jockley, what exactly did you wind up doing?

Bob

 

[jlockley]

tc.open(":alias:tablename.db")
Just what the man suggested. JLL


tc.edit()
scan tc :
if substr(tc."fieldname",1,1)="'") then
tc."fieldname"=substr(tc."fieldname",2,tc."fieldname".size()-1)
endif
try
tc.unlockrecord()
onFail
; handle errors
endTry
endscan
tc.endedit()
tc.close()

 

[SonOfTed]

Some programming languages have a simple way to refer to special characters such as the apostrophe or a quote symbol. For example, if I am creating a text string with an apostrophe in it:

TEXT = "Joe" + "'" + "s Pizza" = "Joes Pizza"

Some applications don't understand the reference if the special character is also part of its programming language. When that is the case try REPEATING it before you give up. Often times this works:

TEXT = "Joe" + "''" + "s Pizza" = "Joe's Pizza"

 

[BobRodes]

In Transact-SQL, for example the statement

SELECT * FROM customers WHERE lname = 'O''Brien'

will interpret the '' as an apostrophe contained in a string. Also, in VB, a "" is interpreted as a literal quote rather than a string encloser.

Your code snippet is interesting, jl. It's an odd feeling to have been intimately acquainted with a syntax, to understand it perfectly when you see it, and to not be able to write it! Thanks for sharing.

Bob

 

[jlockley]

I am afraid it wasn't mine. It is Tony McGuire's. Please see star.

 

[TonyMcGuire]

SonOfTed:
The issue wasn't recognizing the " ' " in the query.

The issue was leaving everything BUT that single quote in the field.

You can recognize the ', but you can't use a QBE query to tell Paradox to leave everything but that character (or any other) in the field.

You can use SQL, or you can do a scan or while loop and use standard OPAL to do the mincing.

In Paradox, special characters (and others, in cases) are usually escaped with a backslash (\).

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[BobRodes]

Oo. Sorry! Your code snippet is interesting, Tony.

Bob

 

[TonyMcGuire]

Well, I've been at Paradox for 16+ years, 14 of them as my job.

I've probably mangled code enough so that you would NOT be able to figure it out.

Heck, I've not been able to figure some of it out, revisiting it later

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[BobRodes]

I started with Paradox 2.0, and worked with it a great deal until 1996. So, I had pretty strong exposure to early Windows versions. It was with ObjectPAL that I first learned terms like cursor, method, dot notation, and the like. Then, I started learning VB, and haven't used Paradox in ten years.

Bob

 

[TonyMcGuire]

"Then, I started learning VB, ..."

My condolences.

Is there an address where I can send a card?




Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.