Ok, I've painted myself in the corner and before I take out a chain saw
and cut out a door, I wanted to check to see if there is an SQL solution.

I have a memo field that has paragraph text and I want to remove only
text between two strings. The text I want to remove starts with
"[start]" and ends with "[end]". There is paragraph text between the two
search strings. I need a wild card removal doo-hinky that will allow me
to replace "[start]%[end]" with "" and it won't affect text outside of
this wildcard range.

Example:
"this is [start]the text to remove in[end]my example" to produce:
"this is my example".

Is there such a doo-hinky or do I need to get gas for the chainsaw and
start ripping out a door (use a table and do it manually)?

TIA
Dave

Dave


You can do it (I think) using a combination of SUBSTRING and POS. Basic approach is similar to the pascal one and I've used it once in the dim and distant past.

Basic approach is along the lines of

UPDATE table SET field =

SUBSTRING(field,1,POS(field,'[START]')-1)+SUBSTRING(field,POS(field,'[END]')+5,LENGTH(field)-POS(field,'[END]'))

probably wrong, and might not work on a memo field but hopefully you get the idea.

The other approach would be to write a custom function.

Roy Lambert

Roy Lambert wrote:

> Dave
>
>
> You can do it (I think) using a combination of SUBSTRING and POS. Basic approach is similar to the pascal one and I've used it once in the dim and distant past.
>
> Basic approach is along the lines of
>
> UPDATE table SET field =
>
> SUBSTRING(field,1,POS(field,'[START]')-1)+SUBSTRING(field,POS(field,'[END]')+5,LENGTH(field)-POS(field,'[END]'))
>
> probably wrong, and might not work on a memo field but hopefully you get the idea.
>
> The other approach would be to write a custom function.
>
> Roy Lambert
>

Roy,
    Ahhh, well that's close but no cigar. There are [Start1]..[End]
and [Start2]..[End] etc so your example will work if the [Startx] is the
first one in the memo, otherwise it won't match up the proper "[End]"
word. That's ok. I chickened out and wrote a custom routine to do it
because I didn't think SQL was up to it. I also don't like to use an
update SQL on something this complicated and cross my fingers and hope
it works. Thanks anyways for the effort.

Dave

Dave


Well if you'd told me the full story to start with

Roy Lambert

Roy Lambert wrote:

> Dave
>
>
> Well if you'd told me the full story to start with
>
> Roy Lambert
>
Roy,
   Now where's the fun in that??? <lol>

Dave