VFP 9 String Replace Within a String

Posted on 2012-08-11
Last Modified: 2012-08-12
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.
Question by:formadmirer
    LVL 29

    Expert Comment

    by:Olaf Doschke
    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.
    LVL 29

    Expert Comment

    by:Olaf Doschke
    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.
    LVL 27

    Assisted Solution

    I wrote this function to remove all HTML tags:

    LOCAL lt, gt
    DO WHILE '<' $ cHTML
          lt = AT('<',cHTML,1)
          gt = AT('>',cHTML,1)
          cHTML = SUBSTR(cHTML,1,lt-1) + SUBSTR(cHTML,gt+1)

    Author Comment

    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'.
    LVL 29

    Accepted Solution

    >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.

    Author Comment

    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.

    Author Closing Comment

    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.
    LVL 29

    Expert Comment

    by:Olaf Doschke
    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.
    LVL 29

    Expert Comment

    by:Olaf Doschke

    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.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: (http://msd…
    It Is not possible to enable LLDP in vSwitch(at least is not supported by VMware), so in this article we will enable this, and also go trough how to enabled CDP and how to get this information in vSwitches and also in vDS.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now