Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2011-10-17
Medium Priority
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
  • 2
LVL 35

Expert Comment

by:Paul MacDonald
ID: 36980621
Unless I misunderstand the question, I'd use
 Replace(SourceString, StringToFind, ReplacementString)

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

Author Comment

ID: 36980718
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 86

Accepted Solution

Mike Tomlinson earned 1000 total points
ID: 36980817
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 35

Assisted Solution

by:Paul MacDonald
Paul MacDonald earned 1000 total points
ID: 36980969
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.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

581 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