Replace command

[rene316]

Hello,
Using the "replace" command. How can I replace characters within the field with other characters.

Example:
name field has "John Smith - Registered Voter"

the "- Registered Voter" is on every record.

I want to get rid of "- Registered Voter" so it will read
"John Smith"

Keep in mind the field is variable length, so I can't just truncate the field using the "modi stru" command. I know there is a way to do it, I have done it before, but it hasn't come up in a long time, so I forgot the line command. I believe it was the "replace" command, but I am not sure.

Thank You.

 

[Gweniviere]

Try something like this....


If the field name is FLD001

REPLACE FLD001 WITH LEFT(FLD001, AT('-',FLD001-1) FOR ALL

That should replace the field with all contents left of the hyphen.

 

[torturedmind]

or you could try this...

REPLACE ALL fld001 WITH STRTRAN(fld001, "- Registered Voter", "&quot

lemme know if this helped.

torturedmind

 

[Mike Gagnon]

Gweniviere

REPLACE FLD001 WITH LEFT(FLD001, AT('-',FLD001-1) FOR ALL

According to my experience the above line won't work. There is aleast in closing braket missing. FOR ALL is not a parameter of REPLACE. Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[Gweniviere]

Mike

You're right. There is a closing paren missing and the FOR ALL is incorrect as well. Thank you for pointing that out.

Gwen

 

[Mike Gagnon]

rene316

You could also use an SQL update:

UPDATE myTable1 set FLD001 = LEFT(FLD001,AT("-",FLD001,1)-1)

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[rgbean]

Mike,
The only problem with your code, is that is may cause problems with hyphenated names. e.g.
"Courtney Thorne-Smith - Registered Voter"
would now be:
"Courtney Thorne"

Rick

 

[Mike Gagnon]

rgbean

True, go to court and have your name legally change to remove the hyphen. ;-)
Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[monikai]

Hi !!
The court have much work....
If one hyphen in name:
UPDATE myTable1 set FLD001 = LEFT(FLD001,AT("-",FLD001,2)-1)
If not - scan file and find last hyphen in the field.
It's OK ?
Kind regards from Warsaw !!!!!
Monika (monikai@yahoo.com)

 

[nl]

rene316,

Assume field name is [name]
The fixed string is at the right , let it be [fs]

name="John Smith - Registered Voter"
fs="- Registered Voter"
replace name with stuff(name,rat(fs,name),len(fs),"&quot

Now, name will be "John Smith"

Norman

 

[rene316]

Thank you to all who have responded. I will try them all and see which one works best.

 

[Bada]

rene
browse the database
create a field nameonly
replace all nameonly with name
browse field nameonly
ctrl+f
find = '- Registered Voter'
replace with ' '

Bada