updating substrings in mysql

how does one go about updating a substring in mysql
e.g.

I want to change all occurences of Gmbh to GMBH within a field string.

BMW gmbh
BMW Gmbh
Mercedes Gmbh
Joe Blogg Gmbh & Mercedes Ltd

get changed to

BMW GMBH
BMW GMBH
Mercedes GMBH
Joe Blogg GMBH & Mercedes Ltd

I know how to do this in PHP, but is there a way of doing it in MySQL
natively?

re: updating substrings in mysql

kaptain kernel wrote:[color=blue]
> I want to change all occurences of Gmbh to GMBH within a field string.[/color]

Maybe like this:


-- SQL --
create table `test`.`xx` (
`id` int (2) UNSIGNED NOT NULL AUTO_INCREMENT ,
`description` varchar (32) NULL ,
PRIMARY KEY ( `id` )
)


insert into `test`.`xx` (description) values
('BMW gmbh'), ('BMW Gmbh'), ('Mercedes Gmbh'), ('Joe Blogg Gmbh &
Mercedes Ltd')


select * from xx


update xx
set description = INSERT(description, INSTR(description, 'gmbh'), 4,
'GMBH')
where LOCATE('gmbh', description)>0


select * from xx
-- END SQL --



HTH
--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--

re: updating substrings in mysql

That is most excellent - thanks. i wasn't aware of the LOCATE function.
This will save a LOT of time (and PHP processing...)



Pedro Graca wrote:[color=blue]
> kaptain kernel wrote:[color=green]
>> I want to change all occurences of Gmbh to GMBH within a field string.[/color]
>
> Maybe like this:
>
>
> -- SQL --
> create table `test`.`xx` (
> `id` int (2) UNSIGNED NOT NULL AUTO_INCREMENT ,
> `description` varchar (32) NULL ,
> PRIMARY KEY ( `id` )
> )
>
>
> insert into `test`.`xx` (description) values
> ('BMW gmbh'), ('BMW Gmbh'), ('Mercedes Gmbh'), ('Joe Blogg Gmbh &
> Mercedes Ltd')
>
>
> select * from xx
>
>
> update xx
> set description = INSERT(description, INSTR(description, 'gmbh'), 4,
> 'GMBH')
> where LOCATE('gmbh', description)>0
>
>
> select * from xx
> -- END SQL --
>
>
>
> HTH[/color]