Need to remove a string of data in a text field if it matches a particular string

Posted on 2011-10-17
Last Modified: 2012-05-12
The application I am working on stores multiple selection field values in a text field in a database in this format:


The values above are references to records in a different table.  What I need to do is see if the value I have, say, "709" is in the string (above) and, if it is, then replace the string with this value (and its preceding comma) removed, e.g., ,707,708,710,
Question by:impala6
    LVL 33

    Expert Comment

    Unless I misunderstand the question, I'd use
     Replace(SourceString, StringToFind, ReplacementString)

     Replace(SourceString, ", 709", "")

    Author Comment

    Let me try to clarify - I have no way of knowing whether the value would be "709" or "1113" so I need a variable that represents that value I'm searching for.  When I find that variable's value in the string, I need to rewrite the string excluding that value.
    LVL 85

    Accepted Solution

    First wrap your value in commas, then replace it with a comma:

    Dim records As String
    records = ",707,708,709,710,"

    Dim field As String
    field = "709"

    records = Replace(records, "," & field & ",", ",")

    *This assumes that you always have that leading and trailing comma on your original records list.

    LVL 33

    Assisted Solution

    Yes, something like what [Idle_Mind] posted.

    Dim records As String
    records = ",707,708,709,710,"

    Dim field As String
    field = "709"

    records = Replace(records, "," & field , "")

    ...since you only wanted to remove the leading comma and replace the whole thing with nothing.  Note this REPLACE presumes there's no space between the leading comma and the value to be removed.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

    794 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

    17 Experts available now in Live!

    Get 1:1 Help Now