need to scan a table and replace field values ending with letters with the same value w/o letter 4

[titoneon]

Hello,
I have a table where, one of the field is named "draw_no", field length is 7 and it is character field. and the values could be like shown below.

118845
118845A
118845B
118845C
8845A
8845B
833X

I need to find the way to scan the whole table and replace the field values, the one with letters, to be w/o the letter, except those ending on "X"
Can anyone help me ?
Thanks a lot

 

[newtofoxpro]

Is this do you need...

CREATE CURSOR myCursor (draw_no c(7), OldValue c(7))
INSERT INTO myCursor (draw_no) VALUES ("118845")
INSERT INTO myCursor (draw_no) VALUES ("118845A")
INSERT INTO myCursor (draw_no) VALUES ("118845B")
INSERT INTO myCursor (draw_no) VALUES ("118845C")
INSERT INTO myCursor (draw_no) VALUES ("8845A")
INSERT INTO myCursor (draw_no) VALUES ("8845B")
INSERT INTO myCursor (draw_no) VALUES ("833X")
replace OldValue WITH Draw_no ALL
replace draw_no WITH ALLTRIM(STR(VAL(draw_no))) FOR !"X"$draw_no

 

[Mike Lewis]

How about something like this (not tested):

SELECT TheTable
REPLACE ALL Draw_No WITH LEFT(Draw_No, LEN(Draw_No) - 1) ;
  FOR ISALPHA(RIGHT(Draw_No, 1)) AND RIGHT(Draw_No, 1)<> "X"

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, your own solution works ok. Take a look at CHRTRAN, eg to only allow digits and X:

REPLACE ALL draw_no WITH CHRTRAN(draw_no, CHRTRAN(draw_no, "0123456789X",""),"")

The inner CHRTRAN results in any unallowed chars by removing all allowed chars 0-9 and X from the field value. The outer CHTRAN then removes those non allowed chars from the field.

This is somewhat less strict, as it would also keep the X in "83X3", but if you don't have any such case it also cleans your data from any non allowed letter.

Bye, Olaf.

 

[newtofoxpro]

Both post's result are different so choose as per your needs.

 

[Mike Lewis]

Both post's result are different so choose as per your needs.

Actually, there's a mistake in mine. I should have ALLTRIM'd Draw_no.

In any case, I think I prefer the other two offerings.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[titoneon]

Ok Guys,
I will test them in my end, i am sure they all will work , thanks a lot

 

[titoneon]

Well in reality i need to show in a cursor the original field, plus the original field with the replacement values too

 

[Mike Lewis]

Well in reality i need to show in a cursor the original field, plus the original field with the replacement values too

NewToFoxpro's solution will do that. But the others can easily be adapted to do so as well.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[titoneon]

No problem, i got it
Thanks

 

[titoneon]

I am using this code and seen ok, but please look into the below notes and code
as i want to accomplish this too

SELECT draw_NO AS OrigDrawno, draw_no, ball_no FROM temjob INTO CURSOR junk readwrite
  SCAN
	replace draw_no WITH ALLTRIM(STR(VAL(draw_no))) FOR !"X"$draw_no
 ENDSCAN
BROWSE

But also i need to find duplicated records that has the same "ball_no"
for those draw_no, that we already removed the letter at the end.
but i need also to have a field with the original value, that is the reason i created the "OrigDrawno" above.
so i did this but i am getting error cause the "Origdrawno" is not correct in the below code.
Also i guess i will need to "index on ball_no tag unique" to display in the below sql the correct
duplicated records

SELECT origdrawno, DRAW_NO+" "+BALL_NO AS X,  FROM Junk;
WHERE JOB_NO="118845"  GROUP BY X;
 HAVING COUNT(DRAW_NO+BALL_NO) > 1 INTO CURSOR RESULTS

How can i display int the "Results" cursor the "OrigDrawno" too ?
Thanks a lot

 

[Mike Lewis]

You've got a couple of problems with this code.

First, there is a spurious comma here: [tt]AS X, FROM Junk[/tt]

More importantly, your grouping won't work (unless you have ENGINEBEHVIOR set to 7, which is not recommended).

The following will fix it:

SELECT [b]MAX([/b]origdrawno[b])[/b], DRAW_NO+" "+ BALL_NO AS X FROM Junk;
WHERE JOB_NO="118845"  GROUP BY X;
 HAVING COUNT(DRAW_NO+BALL_NO) > 1 INTO CURSOR RESULTS

Finally, you don't need to index anything. To display the results in a specified sequence, use the ORDER BY clause.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You also can use REPLACE ALL as has been given by us, instead of SCAN...ENDSCAN and only replace in single records.
It's only one REPLACE ALL, no need to SCAN yourself, the ALL will do that.

And aside of that, you can simply put the expression in the query by using IIF:

SELECT draw_NO AS OrigDrawno, IIF("X"$draw_no, draw_no, PADR(STR(VAL(draw_no)),LEN(draw_no))) as draw_no, ball_no FROM temjob INTO CURSOR junk readwrite

I don't know how you will filter for Job_no = "118845" if there is no Job_no field in the table.

Bye, Olaf.

 

[titoneon]

Olaf,
Yes, i removed the Job_no from there already but i need to display in another cursor after i get the cursor Junk result those existing duplicated records where Ball_no are duplicated and be able to show them.

draw_NO AS OrigDrawno, the Draw_no w/o the letter and the ball_no and this code is not showing me that, it is only showing me, one record where the ball_no is duplicated, i need to actually show all the records where ball_no is duplicated

Example
Origdrawno draw_no ball_no
118845B 118845 110
118845 118845 110
I need to be able to show in a new cursor wherever 110 is more than once as above shown
When i run this code below, only one 110 record is shown

SELECT MAX(origdrawno), DRAW_NO+" "+ BALL_NO AS X FROM JUNK;
 GROUP BY X  HAVING COUNT(DRAW_NO+BALL_NO) > 1 INTO CURSOR RESULTS

What else do you think i need or what do i have wrong ?
Thanks

 

[Olaf Doschke]

>When i run this code below, only one 110 record is shown
That's the intention of a group by you only get one record per group. The HAVING condition makes sure you only get the ball_no's for which there were 2 or more records (>1) with same combination of draw_no+ball_no. If that's not what you want then don't group by.

Bye, Olaf.
.

 

[vgulielmus]

Not sure if this is want you seek, but please adjust it according to your needs :

SELECT j1.origdrawno, j2.X ;
	FROM junk j1,;
	(SELECT DRAW_NO,BALL_NO,JOB_NO, DRAW_NO+" "+BALL_NO AS X  FROM Junk GROUP BY 1,2,3,4 HAVING COUNT(DRAW_NO+BALL_NO) > 1 WHERE JOB_NO="118845") J2 ;
	WHERE j1.DRAW_NO=j2.DRAW_NO AND j1.BALL_NO=j2.BALL_NO AND j1.JOB_NO=j2.JOB_NO &&;
	 INTO CURSOR RESULTS

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[titoneon]

Mike, Olaf
Mike's code is perfect, it is not a problem with that, just that i wanted to show each specific instance where "ball_no is duplicated"
Olaf the code you gave me here

SELECT draw_NO AS OrigDrawno, IIF("X"$draw_no, draw_no, PADR(STR(VAL(draw_no)),LEN(draw_no))) as draw_no, ball_no FROM temjob INTO CURSOR junk readwrite

for a reason if a value of the DRAW_NO is equal to "118845A" or "118845", i will get "118" and not "118845" or
if the value is "8346A", i will get "8"
Thanks a lot

 

[Mike Lewis]

.. if a value of the DRAW_NO is equal to "118845A" or "118845", i will get "118" and not "118845" or
if the value is "8346A", i will get "8"

That might be something to do with LEN(draw_no) not giving the correct figure. You could try changing this:

[tt]PADR(STR(VAL(draw_no)),LEN(draw_no)))[/tt]

to this:

[tt]TRANSFORM(draw_no, "9999999")[/tt]

(where "9999999" has the same number of 9s as the maximum length of draw_no).

But I'm not sure about this. If Olaf tells you something different, he's right and I'm wrong.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[titoneon]

Mike,
you are correct, i replaced that part of the code by the one you wrote and both columns OrigDrawno and draw_no shows the same, so
something is missing in his code and replacing PADR(STR(VAL(draw_no)),LEN(draw_no))) to this: TRANSFORM(draw_no, "9999999"), it only making the draw_no column the same as the OrigDrawno, i don't want to create a big deal on this, actually i just trying to learn from you guys new tricks but the TRANSFORM(draw_no, "9999999") does not make the column to remove the letter from the end but at the same time display the whole value in this field
Thanks so much for always been there to help very appreciated

 

[titoneon]

Mike,
i just replaced PADR(STR(VAL(draw_no)),LEN(draw_no)) with this ALLTRIM(STR(VAL(draw_no))) and it works, like a charm, i have to assume, that you or Olaf since you are pretty busy, did not test it but i am 1000% sure you guys are correct, always looking for learning from the Masters
Thanks