use substr with replace

[shangrilla]

I want to replace the first 6 characters in my table with a variable, but I keep gettting errors when I use susbtr() with replace.

replace substr(myTable.myField,1,6) with myVar all in myTable

The field is character so thats not the problem. Can I not use susbtr() with Replace. If not please suggest an alternative.

 

[wgcs]

Substr does NOT work like MID$() does in VB, SubStr is purely an output function.
You want:

replace myTable.myField with myVar+Substr(myTable.myField,7) all in myTable


or

replace myTable.myField with Stuff(myTable.myField,1,6,myVar) all in myTable

 

[DSummZZZ]

You need to do it more like:

REPLACE ALL myTable.myField WITH myVar + ;
substr(myTable.myField, 7)


Your variable plus the old field starting after position 6.

Dave S.

 

[shangrilla]

Thanks Guys.

 

[mm0000]

Assuming the field length is 10 characters long the syntax would be - replace all mytable.mfield with substr(myvar,1,6)+ substr(mytable.mfield,7,3)though you would have to modify the command based on the field length. If the length of myvar was exactly 6 characters long then the command would be replace all mytable.mfield with myvar+ substr(mytable.mfield,7,3). If the length of myvar is less than 6 you would need to stuff characters.

 

[shangrilla]

Hi All:

I have myVar declared as a public variable but for some reason the program is not remembering whats stored in it.

public myVar
myVar = substr(anotherTable.anotherfield,1,6)

REPLACE ALL myTable.myField WITH myVar +;
substr(myTable.myField, 7)
It only shows the characters 7 onwards. The first 6 characters that come from myVar are not there??????

 

[wgcs]

add, before the REPLACE ALL, the command:
WAIT WINDOW [MyVar="]+myVar+["]

and see what it shows... my guess is that the command "myVar = substr(anotherTable.anotherfield,1,6)" is not working (perhaps because "anotherTable" is at EOF())

 

[Steve Bowman]

Maybe it is!

I would check the value of
substr(anotherTable.anotherfield,1,6),when this occurs.

It is very rare that the contents of a variable get misplaced. Steve Bowman
steve.bowman@ultraex.com

 

[ramani]

HI Shangrilla,

May be this myVar is just blank because the another.Table is EOF() or BOF(). So

public myVar
SELECT anotherTable
LOCATE && or seek the record wanted
myVar = substr(anotherTable.anotherfield,1,6)
SELECT myTable
REPLACE ALL myTable.myField WITH myVar +;
substr(myTable.myField,7)

Hope this helps you ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com