How To Replace Certain Part of Field 1

[byrne1]

How can I replace a portion of text in a field with something else? In my ITEMS table there is a field called DESCR. Some of the values in this field contain text strings that I want to replace. For instance, there are a number of items that contain the text " (S)". I want to remove this text from any items that have it.

Can someone help me develop the SQL script that will do this? TIA!!!

 

[dgillz]

I was able to do this in MS Access with the following code:

UPDATE arcussfil_sql SET Arcusfil_sql.Cus_Name = Replace([arcusfil_sql]![Cus_Name],"(S)","")

I was unable to get anything like this to work in Pervasive, however I'd sure like to know if it is possible.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.srhconsulting.com

 

[TomKane]

Is the (S) in the same place every time? Or could it be anywhere in the field?

 

[byrne1]

The (S) is always at the end of the field value.

 

[dgillz]

I used the following successfully:

UPDATE "ARCUSFIL_SQL" set cus_name=left(cus_name,(length(cus_name)-3))

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.srhconsulting.com

 

[byrne1]

Thanks DGILLZ! Since the string I wanted to replace was not present in each value, I modified your script as follows:

update "ITMDESCR" set description2=left(rtrim(description2),length(rtrim(description2))-4) where right(rtrim(description2),3)='(S)'

That did the trick!