delete from a table records with some characters not needed 2

[Ernesto_Paez]

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

So i have these values in the table

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

 

[Tore Bleken]

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

 

[GriffMG]

arguably:

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

Regards

Griff
Keep [Smile]ing

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. ​

 

[Olaf Doschke]

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.

 

[Ernesto_Paez]

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

 

[Tore Bleken]

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.

 

[jmcd0719]

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

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

 

[Mike Gagnon]

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 first.

 

[Ernesto_Paez]

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.

 

[Olaf Doschke]

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":

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.

 

[Mike Lewis]

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:

[tt]REPLACE ALL MyDBF WITH STRTRAN(MyDBF, "A", "")[/tt]

But that's not what you said originally.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

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.

 

[Mike Lewis]

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

 

[Ernesto_Paez]

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

 

[Scott24x7]

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."

 

[Olaf Doschke]

Well, then you have your solution with Griffs

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.

 

[Tore Bleken]

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.

 

[GriffMG]

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

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 [Smile]ing

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. ​

 

[Ernesto_Paez]

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

 

[Scott24x7]

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."