String Command to Remove Text RRS feed

  • Question

  • Hello

    Thought I'd see if anyone can help me out there.

    In my table, I have a field that contains text similar to the following.


    The big brown dog is happy. He is brown and black and lives at 123 Elm Street.

    The little puppy dog is also happy. He is white and brown and is currently at the lake.

    I'm familiar with the REPLACE STRTRAN command.

    If I wanted to replace the word dog with the word mut...I might use a command like this.

    This works great


    What I need though is a command that will remove all of the text AFTER a specific string of text.

    In my example above: I want to remove the words "He is" and all of the text AFTER these words.

    Any help would be appreciated.

    Mark E

    • Edited by M E Evans Wednesday, April 10, 2019 8:46 PM
    Wednesday, April 10, 2019 8:43 PM

All replies

  • Hi Mark E,

    you could use STREXTRACT for this by using it like this:

    STREXTRACT( FIELDNAM , [] , [He is] )

    However, your example doesn't tell if those two lines are two or one record. If it is one record and you want to change it into this:

    The big brown dog is happy.
    The little puppy dog is also happy.

    then you might need a function that adds a GETWORDCOUNT loop around the STREXTRACT and you'd call that function within your REPLACE.

    FUNCTION StringRemover
    	LPARAMETERS vValue as String
    	LOCAL lcReturn as String, liLoop as Integer
    	lcReturn = []
    	FOR liLoop = 1 TO GETWORDCOUNT( m.vValue , CHR( 13 ) )
    		lcReturn = lcReturn + STREXTRACT( GETWORDNUM( m.vValue , liLoop , CHR( 13 ) ) , [] , [He is] )
    	RETURN lcReturn	

    You'd call this function within your REPLACE like this:



    Gruss / Best regards
    Debugging is twice as hard as writing the code in the first place.
    Therefore, if you write the code as cleverly as possible,
    you are, by definition, not smart enough to debug it. 010101100100011001010000011110000101001001101111011000110110101101110011

    Thursday, April 11, 2019 5:18 AM
  • Hello,

    you may try like this :

    cx = "The big brown dog is happy. He is brown and black and lives at 123 Elm Street."

    ? LEFT( cx, IIF( EMPTY( AT("He is",cx) ),LEN(cx),AT("He is",cx)-1))


    ? IIF( EMPTY( AT("He is",cx) ),cx,LEFT(cx,AT("He is",cx)-1))

    = repl fieldname with  IIF( EMPTY( AT("He is",fieldname) ),fieldname,LEFT(fieldname,AT("He is",fieldname)-1))



    Thursday, April 11, 2019 6:57 AM
  • I like the simple base level solution of using STRECTRACT(), as it shows how moving the focus of a task can make it simpler.

    Instead of trying to find words after some given text to delete them focus on the inverse, you want to keep all up to "He is", that makes "He is" to a delimiter of the text you want to actually keep and the rest falls away as you don't care about it.

    When your only tool is STRTRAN you also have to extend your toolbelt to make somthing off of that idea in general, STREXTRACT is not common knolegde vut functions like AT, finding the position are very common in many languages, so you can assume something like that exists and LEFT() is another common function, that'd be used to get text up to a certain postion.

    Also, you can usually p<rt a task into smaller tasks and a helpful function for that is splitting text into lines with ALINES(), since VFP8 also able to split at other portions, i.e. splitting at "." splits up seentences (if there weren't also "." because of abbreviations).

    Just some thought, I don't want to add another code snippet, you already have good advice.

    Bye, Olaf.

    Thursday, April 11, 2019 9:29 AM
  • No, I can't leave out a solution, but to another problem.

    Context specific search and replace also is possible, it's simple, if you see it, but it'seasily overlooked, when you're stuck on the idea STRTRAN removes specific text and adds a replacement, your replacement of course can contain what you searched.

    So for example if you want to remove all u following q, you can STRTRAN(text,"qu","q") instead of searching all positions of q, then checking whther u follows and then removing it. You're allowed to put back what you first removed and thus only have context specific search&replace without any fancy code.

    One way to put this into context, you could do a 4 step data cleaning this way:

    1. STRTRAN(text,". He is",". REMOVE: He is")

    2.  BROWSE FOR "REMOVE" $ text

    3. decide (manually or with another REPLACE about another rule) where REMOVE now should be taken as end delimiter of text to keep (or start delimiter of text to delete) and manually delete the word, where you want to keep the "He is" sentences.

    4. Finally remove everything after the "REMOVE" markers you introduced.

    Bye, Olaf.

    PS: indeed there also is STUFF to find something and then decide what to do at thst position, cen be used to add REMOVE, too, but is a less common function, the context sepcific replace is doable in many SQL dialects and programming languages.

    • Edited by OlafDoschke Thursday, April 11, 2019 12:51 PM
    Thursday, April 11, 2019 9:39 AM
  • thanks Tom

    Sorry about my example. 

    These would be in two separate lines for two separate records

    Thursday, April 11, 2019 1:26 PM
  • Tom

    I think you hit it.

    I used your code of....

     repl fieldname with  IIF( EMPTY( AT("He is",fieldname) ),fieldname,LEFT(fieldname,AT("He is",fieldname)-1))

    Seems to do the trick.

    I am forever greatful


    • Edited by M E Evans Thursday, April 11, 2019 1:46 PM
    Thursday, April 11, 2019 1:28 PM
  • Hi,

    Is your problem solved? If so, please mark the useful replies as answers.  

    Best Regards,


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Wednesday, April 17, 2019 6:18 AM