INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

delete from a table records with some characters not needed
2

delete from a table records with some characters not needed

delete from a table records with some characters not needed

(OP)
Hi,
I have a table with two fields
field1 =mydbf C(40)
field2 = tick L

So i have these values in the table

CODE -->

MYDBF                  TICK
100013.DBF              F
100013A.DBF             F
100174.DBF              F
100174A.DBF             F 

I want get rid of the records with the letter "A"
thanks in advance

RE: delete from a table records with some characters not needed

Delete for 'A'$mydbf

Or even simpler, since it's "self contained" and only deletes record where the A comes last:
Delete from yourtable where 'A.DBF'$mydbf

RE: delete from a table records with some characters not needed

arguably:

CODE

delete for "A.DBF"$UPPER(MYDBF) 

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: delete from a table records with some characters not needed

We can make this even more complicated, eg check if there is a corresponding row with the same name without A. I would for sure search for "A.DBF" and not just A, because just looking for "A" seems oversimplification to me, you look for a suffix in the STEM part of the file name, so you look for file names ending in A.DBF and that should be searched.

Enough said, you can surely speak for yourself, if that satisfies the search condition or not.

Bye, Olaf.

RE: delete from a table records with some characters not needed

(OP)
i am typing help STEM in the command window and not able to find it, so don't know how to use it
vfp 9.0 sp2
Thanks

RE: delete from a table records with some characters not needed

The stem is the first part of a file name, and the last part is the extension. For instance 123.DBF where 123 is the stem, and DB is the extension.

RE: delete from a table records with some characters not needed

delete for at('A',upper(mydbf))>0

This will delete for any place in the filename that A is located.

RE: delete from a table records with some characters not needed

jmcd0719
Your are not specifying the field that may or may not contain the A value

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483: How to get the best response from the forum first.

RE: delete from a table records with some characters not needed

(OP)
i just need to delete the letter A ,after the last number, not wherever a letter "A" could be, i know this is a character field, so if i have a table named assy.dbf, this value will be deleted from mytable.mydbf
'mydbf' it is a field from a table named mytable.

RE: delete from a table records with some characters not needed

STEM was not a hint on a command to use, but is just the term for the filename part before the file extension. This was just description. To search for A as end of the stem name, you can search for "A.DBF" just like tblekens solution only searches for "A":

CODE

Delete for 'A.DBF'$mydbf 

Just as Griff said. That's all. Just a more precise observation of what you might need as tblekens solution will remove any records with 'A's in filenames, eg both ABC123.DBF and ABC123A.DBF are deleted from the data, not only ABC123A.DBF. Your data only contains filenames with digits and some As, but I never trust just seeing some samples. You should know, whether that plays a role, but you might not have thought and looked so far into it. By the way such file names are allowed, but once you open up 1234.DBF you don't get alias name 1234, names can't begin with a digit, so that's bad file name choices.

If you want to know where that term for the part before the file extension comes from, look at JUSTSTEM() - which you find, when you use the search feature of the help. It's only related in terminology, has nothing to do with any sql or xbase table command you need. Though it can be used, as in DELETE FOR UPPER(RIGHT(JUSTSTEM(mydbf),1))="A", which would be a very precise way of removing records of dbf fies ending in A before the file extension, no matter what extension, if there is an extension at all. Even when you would store 100174A without the .DBF extension, UPPER(RIGHT(JUSTSTEM("100174A "),1))="A" is true, so in that regard it acts like a RTRIM, too, besides cutting to the first "." in a string.

Bye, Olaf.

RE: delete from a table records with some characters not needed

Quote:

i just need to delete the letter A ,after the last number, not wherever a letter "A" could be,

Do you mean you want to remove the "A" from the field, rather than getting rid of the records where the field contains "A"? If so:

REPLACE ALL MyDBF WITH STRTRAN(MyDBF, "A", "")

But that's not what you said originally.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: delete from a table records with some characters not needed

Indeed, I just read your new post now and it contradicts what you initially wanted. You said you want to get rid of the records with the letter "A", so delete them, not change them. Otherwsie you should have said you want to get rid of the "A"s within the mydbf field.

You only get a precise solution with a precise problem description.

Bye, Olaf.

RE: delete from a table records with some characters not needed

And, if it's right that you now want to delete the letter "A" (not the entire record), some more information would be useful:

- Does the letter "A" only occur once (or zero times) in each record?

- Is the extension always "DBF" or is there a possibility that the extention could contain "A"?

- Is the name always stored in caps? If not, do you also want to delete lower-case "a"?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: delete from a table records with some characters not needed

(OP)
just want to delete from table, the entire record when the value in the field has the letter "A" after the last number
Thanks to everyone

RE: delete from a table records with some characters not needed

CODE

USE <TableName> EXCLUSIVE
SELECT <TableName>
GO TOP
DO WHILE NOT EOF()
   IF OCCURS("A",TableName.FieldName)
      DELETE
   ENDIF
   SKIP
ENDDO

SELECT <TableName>
PACK  && if you want to remove them permanently after the delete 


There may be more elegant solutions, but this one is clear, and easily maintainable.
Where <TableName> is the name of your data table, and .FieldName is the name of the field in the table (you can simply the OCCURS clause to IF OCCURS("A",FieldName) since you already have that table selected, I was just being pedantic.



Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."hammer

RE: delete from a table records with some characters not needed

Well, then you have your solution with Griffs

CODE

delete for "A.DBF"$UPPER(MYDBF) 
This does not only look for A, but for A "after the last number", as that also means it's an A before the .DBF file extension.

Bye, Olaf.

RE: delete from a table records with some characters not needed

Scott,

your program needs ten lines to do exactly the same as I do in one line..... And I don't think your program is more easily maintainable than mine.

RE: delete from a table records with some characters not needed

Doesn't OCCURS() return a numeric value - not a Boolean?

CODE

USE <TableName> EXCLUSIVE
SELECT <TableName>
GO TOP
DO WHILE NOT EOF()
   IF OCCURS("A.DBF",UPPER(TableName.FieldName)) > 0
      DELETE
   ENDIF
   SKIP
ENDDO

SELECT <TableName>
PACK  && if you want to remove them permanently after the delete 

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: delete from a table records with some characters not needed

(OP)
Ok, guys, what about this

Delete For Between(Asc(Right(Juststem(mydbf),1)),65,122)

where 65 is A and 122 is Z(and i know all of you know that), so if i have any letter after the last number and before the .dbf, then that record will be deleted, please correct me if i am wrong.

By Olaf ---> DELETE FOR UPPER(RIGHT(JUSTSTEM(mydbf),1))="A"
i think i copied Olaf approach and make a little adjustment as maybe letter could be lower or upper case, i know i never said about lower case but could be possible, that
is why i did not put the UPPER()
Thanks

RE: delete from a table records with some characters not needed

Griff,
Ah yeah, you're right. Needed to test the return value. Good catch. Was just going off the top of my head...

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."hammer

RE: delete from a table records with some characters not needed

No problem Scott

Nice readable code, I don't think I have ever used OCCURS() before, something new every day.

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: delete from a table records with some characters not needed

Delete For Between(Asc(Right(Juststem(mydbf),1)),65,122) can be replaced by this:

Delete For IsAlpha(Right(Juststem(mydbf),1))

RE: delete from a table records with some characters not needed

Quote:

Delete For Between(Asc(Right(Juststem(mydbf),1)),65,122)

Well, that will delete records that contain any capital letter - not just "A".

If that's what you want, fine. But it is different from what you originally asked for. And different from what you asked for later in the thread.

Also, BETWEEN(ASC(..., 65, 122)) is unnecessary. You can simply test for ISALPHA(...).


mIKE

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: delete from a table records with some characters not needed

(OP)
Thanks to all, thanks tbleken

RE: delete from a table records with some characters not needed

Hi Tore,
Yeah, I get that it's more lines. But it works in all versions of Fox back to FoxBase Plus... so has the advantage of backward compatibility.
I'm a big fan of reducing the lines of code down to small numbers where it's still readable, or where speed is critical. Your solution is elegant, but requires a deeper knowledge of the language. I still like it though. landfla seems to be a novice to Fox (no offence landfla), so I went with an approach that I thought was more digestible to a novice.

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."hammer

RE: delete from a table records with some characters not needed

Notice, Scott, that DELETE here also is a legacy dbase command and not SQL-DELETE, it uses the FOR clause and not a WHERE clause. So Griffs simple DELETE FOR also is legacy code a VFP novice knowing legacy Foxpro 2.6 has to know, albeit much simpler than a loop. The only "expert" knowlegde is about the $ operator. But it's not hard to look that up.

Bye, Olaf.

RE: delete from a table records with some characters not needed

Scott, I disagree. As far as I can see, both Tore's and Griff's solutions would work in Foxbase Plus - and even further back.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: delete from a table records with some characters not needed

Ok, point taken.

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."hammer

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close