Could you me with a SQL example to remove all text enclosed in
brackets in a field. E.g.:

Before : "blahblah (blah) blah"
After : "blahblah blah"

Note that the text in the brackets is different in each record,
therefore AFAIK an 'update [table] set
[field]=replace([field],"before","after")' will not work - as it won't
accept regex to wildcard that in brackets.


Thanks

eguttridge@gmail.com wrote:
> Could you me with a SQL example to remove all text enclosed in
> brackets in a field. E.g.:
>
> Before : "blahblah (blah) blah"
> After : "blahblah blah"
>
> Note that the text in the brackets is different in each record,
> therefore AFAIK an 'update [table] set
> [field]=replace([field],"before","after")' will not work - as it won't
> accept regex to wildcard that in brackets.
>
>
> Thanks

What's wrong with

[field] = SELECT concat( trim(substring_index( [field] , '(', 1 )) ,
substring_index( [field] , ')' , -1 ) )

On Dec 28, 5:07pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> eguttri...@gmail.com wrote:
> > Could you me with a SQL example to remove all text enclosed in
> > brackets in a field. E.g.:
>
> > Before : "blahblah (blah) blah"
> > After : "blahblah blah"
>
> > Note that the text in the brackets is different in each record,
> > therefore AFAIK an 'update [table] set
> > [field]=replace([field],"before","after")' will not work - as it won't
> > accept regex to wildcard that in brackets.
>
> > Thanks
>
> What's wrong with
>
> [field] = SELECT concat( trim(substring_index( [field] , '(', 1 )) ,
> substring_index( [field] , ')' , -1 ) )

This works well as a select, thanks:
SELECT concat( trim(substring_index( [field] , '(',
1 )) ,substring_index( [field] , ')' , -1 ) ) FROM [table] WHERE
[condition];

- however when trying it as an update, I get "1093 - You can't specify
target table [table] for update in FROM clause"
UPDATE [table] SET [field]=(SELECT
concat( trim(substring_index( [field] , '(',
1 )) ,substring_index( [field] , ')' , -1 ) ) FROM [table] WHERE
[condition]);

Any ideas?

On Fri, 28 Dec 2007 17:07:47 -0000, Paul Lautman wrote:
> eguttridge@gmail.com wrote:
>> Could you me with a SQL example to remove all text enclosed in
>> brackets in a field. E.g.:
>>
>> Before : "blahblah (blah) blah"
>> After : "blahblah blah"
>>
>> Note that the text in the brackets is different in each record,
>> therefore AFAIK an 'update [table] set
>> [field]=replace([field],"before","after")' will not work - as it won't
>> accept regex to wildcard that in brackets.
>>
>>
>> Thanks
>
> What's wrong with
>
> [field] = SELECT concat( trim(substring_index( [field] , '(', 1 )) ,
> substring_index( [field] , ')' , -1 ) )

How many sets of parenthesized strings does this remove from field: 1 or
many?

--
Judging by this particular thread, many people in this group spent their
school years taking illogical, pointless orders from morons and having
their will to live systematically crushed. And people say school doesn't
prepare kids for the real world. -- Rayner, in the Monastery

On Dec 28, 5:52pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> On Fri, 28 Dec 2007 17:07:47 -0000, Paul Lautman wrote:
> > eguttri...@gmail.com wrote:
> >> Could you me with a SQL example to remove all text enclosed in
> >> brackets in a field. E.g.:
>
> >> Before : "blahblah (blah) blah"
> >> After : "blahblah blah"
>
> >> Note that the text in the brackets is different in each record,
> >> therefore AFAIK an 'update [table] set
> >> [field]=replace([field],"before","after")' will not work - as it won't
> >> accept regex to wildcard that in brackets.
>
> >> Thanks
>
> > What's wrong with
>
> > [field] = SELECT concat( trim(substring_index( [field] , '(', 1 )) ,
> > substring_index( [field] , ')' , -1 ) )
>
> How many sets of parenthesized strings does this remove from field: 1 or
> many?

1

eguttridge@gmail.com wrote:
> On Dec 28, 5:07 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>> eguttri...@gmail.com wrote:
>> > Could you me with a SQL example to remove all text enclosed in
>> > brackets in a field. E.g.:
>>
>> > Before : "blahblah (blah) blah"
>> > After : "blahblah blah"
>>
>> > Note that the text in the brackets is different in each record,
>> > therefore AFAIK an 'update [table] set
>> > [field]=replace([field],"before","after")' will not work - as it
>> > won't accept regex to wildcard that in brackets.
>>
>> > Thanks
>>
>> What's wrong with
>>
>> [field] = SELECT concat( trim(substring_index( [field] , '(', 1 )) ,
>> substring_index( [field] , ')' , -1 ) )
>
> This works well as a select, thanks:
> SELECT concat( trim(substring_index( [field] , '(',
> 1 )) ,substring_index( [field] , ')' , -1 ) ) FROM [table] WHERE
> [condition];
>
> - however when trying it as an update, I get "1093 - You can't specify
> target table [table] for update in FROM clause"
> UPDATE [table] SET [field]=(SELECT
> concat( trim(substring_index( [field] , '(',
> 1 )) ,substring_index( [field] , ')' , -1 ) ) FROM [table] WHERE
> [condition]);
>
> Any ideas?

Yes, don't use a subselect, you don't even need a join.

I already posted the SET clause, that is all you need!

On Fri, 28 Dec 2007 09:44:58 -0800 (PST),
eguttridge@gmail.com wrote:

>On Dec 28, 5:07pm, "Paul Lautman" <paul.laut...@btinternet.com>
>wrote:
>> eguttri...@gmail.com wrote:
>> > Could you me with a SQL example to remove all text enclosed in
>> > brackets in a field. E.g.:
>>
>> > Before : "blahblah (blah) blah"
>> > After : "blahblah blah"
>>
>> > Note that the text in the brackets is different in each record,
>> > therefore AFAIK an 'update [table] set
>> > [field]=replace([field],"before","after")' will not work - as it won't
>> > accept regex to wildcard that in brackets.
>>
>> > Thanks
>>
>> What's wrong with
>>
>> [field] = SELECT concat( trim(substring_index( [field] , '(', 1 )) ,
>> substring_index( [field] , ')' , -1 ) )
>
>This works well as a select, thanks:
>SELECT concat( trim(substring_index( [field] , '(',
>1 )) ,substring_index( [field] , ')' , -1 ) ) FROM [table] WHERE
>[condition];
>
>- however when trying it as an update, I get "1093 - You can't specify
>target table [table] for update in FROM clause"
>
>UPDATE [table] SET [field]=(SELECT
>concat( trim(substring_index( [field] , '(',
>1 )) ,substring_index( [field] , ')' , -1 ) ) FROM [table] WHERE
>[condition]);

You don't need the select to get the value of a column in
an UPDATE statement. This should do:
UPDATE [table]
SET [field]= concat(
trim(substring_index( [field] , '(', 1 )),
substring_index( [field] , ')' , -1 ) )
WHERE [condition]);

>Any ideas?
--
( Kees
)
c[_] Preudhomme's Law of Window Cleaning: It's on the other side. (#512)

On Dec 28, 6:48pm, Kees Nuyt <k.n...@nospam.demon.nl> wrote:
> On Fri, 28 Dec 2007 09:44:58 -0800 (PST),
>
>
>
>
>
> eguttri...@gmail.com wrote:
> >On Dec 28, 5:07pm, "Paul Lautman" <paul.laut...@btinternet.com>
> >wrote:
> >> eguttri...@gmail.com wrote:
> >> > Could you me with a SQL example to remove all text enclosed in
> >> > brackets in a field. E.g.:
>
> >> > Before : "blahblah (blah) blah"
> >> > After : "blahblah blah"
>
> >> > Note that the text in the brackets is different in each record,
> >> > therefore AFAIK an 'update [table] set
> >> > [field]=replace([field],"before","after")' will not work - as it won't
> >> > accept regex to wildcard that in brackets.
>
> >> > Thanks
>
> >> What's wrong with
>
> >> [field] = SELECT concat( trim(substring_index( [field] , '(', 1 )) ,
> >> substring_index( [field] , ')' , -1 ) )
>
> >This works well as a select, thanks:
> >SELECT concat( trim(substring_index( [field] , '(',
> >1 )) ,substring_index( [field] , ')' , -1 ) ) FROM [table] WHERE
> >[condition];
>
> >- however when trying it as an update, I get "1093 - You can't specify
> >target table [table] for update in FROM clause"
>
> >UPDATE [table] SET [field]=(SELECT
> >concat( trim(substring_index( [field] , '(',
> >1 )) ,substring_index( [field] , ')' , -1 ) ) FROM [table] WHERE
> >[condition]);
>
> You don't need the select to get the value of a column in
> an UPDATE statement. This should do:
> UPDATE [table]
> SET [field]= concat(
> trim(substring_index( [field] , '(', 1 )),
> substring_index( [field] , ')' , -1 ) )
> WHERE [condition]);
>
> >Any ideas?
>
> --
> ( Kees
> )
> c[_] Preudhomme's Law of Window Cleaning: It's on the other side. (#512)- Hide quoted text -
>
> - Show quoted text -

Got it - thanks Paul and Kees

Peter H. Coffin wrote:
> On Fri, 28 Dec 2007 17:07:47 -0000, Paul Lautman wrote:
>> eguttridge@gmail.com wrote:
>>> Could you me with a SQL example to remove all text enclosed in
>>> brackets in a field. E.g.:
>>>
>>> Before : "blahblah (blah) blah"
>>> After : "blahblah blah"
>>>
>>> Note that the text in the brackets is different in each record,
>>> therefore AFAIK an 'update [table] set
>>> [field]=replace([field],"before","after")' will not work - as it
>>> won't accept regex to wildcard that in brackets.
>>>
>>>
>>> Thanks
>>
>> What's wrong with
>>
>> [field] = SELECT concat( trim(substring_index( [field] , '(', 1 )) ,
>> substring_index( [field] , ')' , -1 ) )
>
> How many sets of parenthesized strings does this remove from field: 1
> or many?

1, why not try it out?

On Fri, 28 Dec 2007 21:41:46 -0000, Paul Lautman wrote:
> Peter H. Coffin wrote:
>> On Fri, 28 Dec 2007 17:07:47 -0000, Paul Lautman wrote:
>>> eguttridge@gmail.com wrote:
>>>> Could you me with a SQL example to remove all text enclosed in
>>>> brackets in a field. E.g.:
>>>>
>>>> Before : "blahblah (blah) blah"
>>>> After : "blahblah blah"
>>>>
>>>> Note that the text in the brackets is different in each record,
>>>> therefore AFAIK an 'update [table] set
>>>> [field]=replace([field],"before","after")' will not work - as it
>>>> won't accept regex to wildcard that in brackets.
>>>>
>>>>
>>>> Thanks
>>>
>>> What's wrong with
>>>
>>> [field] = SELECT concat( trim(substring_index( [field] , '(', 1 )) ,
>>> substring_index( [field] , ')' , -1 ) )
>>
>> How many sets of parenthesized strings does this remove from field: 1
>> or many?
>
> 1, why not try it out?

Me? I don't really care. I'm not the person asking the original
question. But you asked what might be wrong with such and so, and I
offered a possible gap in your solution: it copes with dropping one set
of paren-substrings, but not more, per iteration. That's often not what
someone's looking to actually do. Not a *problem* per se, but something
to be aware of.

--
When all you have is a hammer, every problem looks like a messiah.

Peter H. Coffin wrote:
> On Fri, 28 Dec 2007 21:41:46 -0000, Paul Lautman wrote:
>> Peter H. Coffin wrote:
>>> On Fri, 28 Dec 2007 17:07:47 -0000, Paul Lautman wrote:
>>>> eguttridge@gmail.com wrote:
>>>>> Could you me with a SQL example to remove all text enclosed
>>>>> in brackets in a field. E.g.:
>>>>>
>>>>> Before : "blahblah (blah) blah"
>>>>> After : "blahblah blah"
>>>>>
>>>>> Note that the text in the brackets is different in each record,
>>>>> therefore AFAIK an 'update [table] set
>>>>> [field]=replace([field],"before","after")' will not work - as it
>>>>> won't accept regex to wildcard that in brackets.
>>>>>
>>>>>
>>>>> Thanks
>>>>
>>>> What's wrong with
>>>>
>>>> [field] = SELECT concat( trim(substring_index( [field] , '(', 1 ))
>>>> , substring_index( [field] , ')' , -1 ) )
>>>
>>> How many sets of parenthesized strings does this remove from field:
>>> 1 or many?
>>
>> 1, why not try it out?
>
> Me? I don't really care. I'm not the person asking the original
> question. But you asked what might be wrong with such and so, and I
> offered a possible gap in your solution: it copes with dropping one
> set of paren-substrings, but not more, per iteration. That's often
> not what someone's looking to actually do. Not a *problem* per se,
> but something to be aware of.

The samples that the OP proffered only had one set of substitution values.

On Sat, 29 Dec 2007 12:27:30 -0000, Paul Lautman wrote:
> Peter H. Coffin wrote:
>> On Fri, 28 Dec 2007 21:41:46 -0000, Paul Lautman wrote:
>>> Peter H. Coffin wrote:
>>>> On Fri, 28 Dec 2007 17:07:47 -0000, Paul Lautman wrote:
>>>>>
>>>>> What's wrong with
>>>>>
>>>>> [field] = SELECT concat( trim(substring_index( [field] , '(', 1 ))
>>>>> , substring_index( [field] , ')' , -1 ) )
>>>>
>>>> How many sets of parenthesized strings does this remove from field:
>>>> 1 or many?
>>>
>>> 1, why not try it out?
>>
>> Me? I don't really care. I'm not the person asking the original
>> question. But you asked what might be wrong with such and so, and I
>> offered a possible gap in your solution: it copes with dropping one
>> set of paren-substrings, but not more, per iteration. That's often
>> not what someone's looking to actually do. Not a *problem* per se,
>> but something to be aware of.
>
> The samples that the OP proffered only had one set of substitution values.

They also only showed the parens in one position. You cleverly avoided
making that assumption, though.

--
10. I will not interrogate my enemies in the inner sanctum -- a small hotel
well outside my borders will work just as well.
--Peter Anspach's list of things to do as an Evil Overlord

Peter H. Coffin wrote:
> On Sat, 29 Dec 2007 12:27:30 -0000, Paul Lautman wrote:
>> Peter H. Coffin wrote:
>>> On Fri, 28 Dec 2007 21:41:46 -0000, Paul Lautman wrote:
>>>> Peter H. Coffin wrote:
>>>>> On Fri, 28 Dec 2007 17:07:47 -0000, Paul Lautman wrote:
>>>>>>
>>>>>> What's wrong with
>>>>>>
>>>>>> [field] = SELECT concat( trim(substring_index( [field] , '(', 1
>>>>>> )) , substring_index( [field] , ')' , -1 ) )
>>>>>
>>>>> How many sets of parenthesized strings does this remove from
>>>>> field: 1 or many?
>>>>
>>>> 1, why not try it out?
>>>
>>> Me? I don't really care. I'm not the person asking the original
>>> question. But you asked what might be wrong with such and so, and I
>>> offered a possible gap in your solution: it copes with dropping one
>>> set of paren-substrings, but not more, per iteration. That's often
>>> not what someone's looking to actually do. Not a *problem* per se,
>>> but something to be aware of.
>>
>> The samples that the OP proffered only had one set of substitution
>> values.
>
> They also only showed the parens in one position. You cleverly avoided
> making that assumption, though.

That's because he said that the text "blah" would be different in every
record. Do keep up!