VFP 9 String Replace Within a String

I'm running a Scan against a table where I'm pulling the description (memo) into a variable and performing a string replace.

lcDescr is a product description of approx 200 words
lcLookForProd is a numeric product ID
lcLinkProductID is a numeric product ID
lcReplaceProdWith is shown below

lcReplaceProdWith = 'Product #<a href="/' + ALLTRIM(purl.keyword) + '">' + lcLinkProductID + '</a>'

STRTRAN(lcDescr, lcLookForProd, lcReplaceProdWith) && make text replacement in description

Open in new window

This works great except my final result is:
Product #<a href="/Speakers">26810</a>26810
which results in Product #2681026810

How to fix this?
(the product numbers vary in length and may have a space, comma, period or < directly after.
Who is Participating?
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
>lcLookForProd = "Product #"

Well, that explains everything. You need to replace lcLookForProd = "Product #26810", so you always first nbeed to add the product number to "Product #" as you put it in yourself you also need to remove it, or you end up with it double.

Bye, Olaf.
Olaf DoschkeSoftware DeveloperCommented:
The 26810 you have in the final result after the closing </a> is not coming in from the STRTRAN, for sure, 100% sure.

What would be interesting is your value of lcLookForProd. The only explanaition I see is, it just goes before the product #, so the repeating product # in the end just comes from you not overwriting it.

eg you have
"product #26810"

But you only replace "product #" with 'Product #<a href="/' + ALLTRIM(purl.keyword) + '">' + lcLinkProductID + '</a>'

Then you keep the initial 26810 and end up with
"Product #<a href="/Speakers">26810</a>26810" which displays as Product #2681026810

That's the only explanaition I have.

Bye, Olaf.
Olaf DoschkeSoftware DeveloperCommented:
Another you wouold need to look into is, what's in lcDescr. On the 1:! html code, not what a webbrowser control displays. You do STRTRAN on that html code level, so you have to replace what's in the html source code, not just what is displayed, of course.

Bye, Olaf.
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

CaptainCyrilConnect With a Mentor Founder, Software Engineer, Data ScientistCommented:
I wrote this function to remove all HTML tags:

LOCAL lt, gt
      lt = AT('<',cHTML,1)
      gt = AT('>',cHTML,1)
      cHTML = SUBSTR(cHTML,1,lt-1) + SUBSTR(cHTML,gt+1)
formadmirerAuthor Commented:
I'm sorry I overlooked that. Here's lcLookForProd:

lcLookForProd = "Product #"

I'll run through the process as I have it now step by step.

First I construct a query to run against a MySQL db using a product number, in this case product #15513. This works fine and gives me the following lcDescr from a memo field:

10w–100w Per Channel power handling capability; 5.5" Polypropylene long-throw Woofer; 1/2" forrofluid-cooled Dome Tweeter ; Imp: 8 Ohm; Freq Resp:;60 Hz–21 Khz ; 2-WAY In-wall Speakers ;  and White ABS Plastic With White Metal Grill.

Dimmensions: 11"h X 7.5"w X 3"d ; Cutout required: 9 5/8" x 7"
3-year Limited Manufacturers Warranty On Parts & Labor ; 1 Pair.

Product #26810

This is for testing purposes only so all I've done is tag the bottom with a product number.

To prevent unnecessary processing I then check the description (lcDescr) to see if it needs further processing:

IF (lcLookForProd $ lcDescr)
      llProdURL = .T.

This works fine as well.

I then proceed to extract the Product # from the description using code provided here a few days ago:

IF llProdURL = .T.

lcLinkProductID = GetWordNum(SUBSTR(lcDescr, AT(lcLookForProd, lcDescr)+9), 1, " ,.<")

Again works fine.

I now perform another query against a MySQL db looking for a keyword using the Product # just acquired. This works fine.

Once I have the keyword I proceed to attempt the string replacement:

lcReplaceProdWith = lcLookForProd + '<a href="/' + ALLTRIM(purl.keyword) + '">' + lcLinkProductID + '</a>'
lcDescr = STRTRAN(lcDescr, lcLookForProd, lcReplaceProdWith)

Which is where I wind up with 'Product #2681026810' displaying on the site,
or 'Product #<a href="/Speakers">26810</a>26810' as is showing in the html.

I need to get rid of the second '26810'.
formadmirerAuthor Commented:
All it took was me writing this out and thinking about it logically. Within a minute of hitting submit I figured it out. The simple solution was modifying lcLookForProd to include the linkProdId, and Voila it worked.

Thanks for the help.
formadmirerAuthor Commented:
CaptainCyril I'm giving you points because I can surely use the code you provided in just a little bit.<br /><br />And Olaf I have no idea how you answered so quickly! All I did was one quick change, ran it and came back to post my results. When I was done I saw you had already posted the answer as well. That's fantastic!<br /><br />Thanks to all who helped.
Olaf DoschkeSoftware DeveloperCommented:
Well, EE is sending out mails and I was in Gmail anyway.

Just one more thought: In an earlier question about finding the "Product#" to extract the numeric value after that occurrance of "Product #" you said there could be places where you have "Product # 12345" instead of "Product #12345" with an additional space between # and the number.

You have of course to watch out for these places. You can extract the text between # and the product number you found out and remove it first, so you always have Product #NNNNN before doing the other STRTRAN you do here in this question.

Here's the code for cleanup of spaces, tabs or whatever is between # and the number:
lcGarbage = STREXTRACT(lcDescr,'Product #',lcLinkProductID,1,0)
lcDescr = STRTRAN(lcDescr,lcGarbage,'')
* and then your
lcLookForProd = "Product #"+lcLinkProductID
lcDescr = STRTRAN(lcDescr, lcLookForProd, lcReplaceProdWith)

Open in new window

Bye, Olaf.

PS: You might want to be cautious with removing lcGarbage, if it's larger than say 5 Bytes:

Because then you most probably have more than one occurrances of Product # in the text and the current productnumber is a secondary occurrance, thus lc"Garbage" remove everything in between the first occurrance of "Product #" with another product number following and the occurrance with the product numer you process now. So in the first place, rather forget about this, you'd need to knwo on which occurrance # you work on, before being able to remove just the right portion of whitespace.
Olaf DoschkeSoftware DeveloperCommented:

much more important than when lcGarbage is a large portion of the text: If it's just a space, you'd replace any space in the text. No forget about that cleanup overall. You'd rather extend the text to replace:

lcGarbage = STREXTRACT(lcDescr,'Product #',lcLinkProductID,1,0)
If Len(lcGarbage)>5
lcLookForProd = 'Product #'+lcGarbage+lcLinkProductID
lcDescr = STRTRAN(lcDescr, lcLookForProd, lcReplaceProdWith)

Open in new window

Bye, Olaf.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.