SQL Statement Replce text in a column

[Craig Keightley]

I am trying to write an sql statement that replaces a string in a column
from one value to another

The table contains email addresses and some start with 'w*******@domain.com'

I want to be able to remove the www. part of the string

any help would be grateful
craig

[Aggro]

Craig Keightley wrote:

I am trying to write an sql statement that replaces a string in a column
from one value to another

The table contains email addresses and some start with 'w*******@domain.com'

I want to be able to remove the www. part of the string

# Do you know the exact string which you want to replace? If yes:
update tablename set email='new address' where email='old address';

# Or do you just know that you want to remove www. on the
# beginning of all rows where it exists?
update tablename
set email=SUBSTRING( email, 5 )
where email like 'www.%';

[Siemel Naran]

"Aggro" <sp**********@yahoo.com> wrote in message news:qJFxc.265

# Or do you just know that you want to remove www. on the
# beginning of all rows where it exists?
update tablename
set email=SUBSTRING( email, 5 )
where email like 'www.%';

There is also REPLACE, which is useful for replacing substrings anywhere in
the string.

set email = REPLACE(email, 'www.', '');

But it will replace www. anywhere in the string, which is not the same thing
as your original code.

Does anyone know if the search string, or the 2nd argument, can be a regular
expression?

[Aggro]

Siemel Naran wrote:

There is also REPLACE, which is useful for replacing substrings anywhere in
the string.

set email = REPLACE(email, 'www.', '');

But it will replace www. anywhere in the string, which is not the same thing
as your original code.

I know this, and I thought that searching for like 'www.%' would be
faster, rather than using a search anywhere in the middle of the string,
because when searching only from the left side, we can use indexes if
they exist.

And also, like you said, replace will also change values in the middle
of the string, and that is not what we want. So I see no reason, why we
should think about how to get replace to work correctly.

[Aggro]

Siemel Naran wrote:

Does anyone know if the search string, or the 2nd argument, can be a regular
expression?

Tried it, and I would say that it is not possible.

If I use something like replace( email, '^www.', '' ), it would find
only rows which start with the "^www." including the first character
(unlike it would work with regular expressions.)

I also tried:

replace( email, email REGEXP '^www.', '' )

But that doesn't either work, because email REGEXP '^www.' would just
return 0 or 1 depending wether the url starts with www. or not. So that
would replace 0 or 1 characters in the string.

I got no other ideas how it cold work.