Replace characters in the middle of a field. 1

[Dimonet]

I have a field that contains information within it that I would like to replace. The following is an example.

111111|\\XXXXXX\XXXXXX\11111

I woould like to replace \\XXXXXX\XXXXXX\ with a new value. I need some help as to how to replace text that is wrapped by other text.

Any help would be appreciated.

Thanks,
Dimo

 

[scriverb]

Use the Replace function:

Replace("abcdefffggghijkl", "fffggg", "fg")

The above example will return the following:

"abcdefghijkl"

post back with questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Dimonet]

Thanks so much for your respone!

I am sorry I forgot to mention one other thing. And I also have a question about you response. The characters before and after the string that I want to replace will not always be the same. They will vary. So I need to know the syntax that will account for that variance. Also could you post what the complete code would look like if the table name were "test" and the field name was "test field".

Thanks again!

Dimo

 

[scriverb]

Let's create a SQl query that displays the field before and after the replacement function:

Select A.[test field], Replace(A.[test field], "[blue]search string[/blue]", "[green]replacement string[/green]") as [Modified_test_field] 
FROM [red]test[/red] as A;

Update the blue and green code with search string and the replacement strings.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Dimonet]

Ok that worked great but how do I get the new value back into the original field labeled "test field" in your example?

Thanks again,
Dimo

 

[scriverb]

You must use an update query to accomplish this:

Update [test] as A Set A.[test field] = Replace(A.[test field], "search string", "replacement string");

This should update the field [test] with the modified value after the Replace function has rebuilt the string.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Dimonet]

Thanks so much that worked great!