I really don't know exactly what to do, I'm a bit new to SQL. Should I use substring
or replace
in my case?
I have a field in my DB called "email", now I wanted to edited all email ending with "email.com" to "email.org". What shall I do?
The following are the syntax of REPLACE statement in MySQL: REPLACE [INTO] table_name(column_list) VALUES(value_list);
UPDATE Syntax Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!
It depends on what you need to do. You can use replace
since you want to replace the value:
select replace(email, '.com', '.org')
from yourtable
Then to UPDATE
your table with the new ending, then you would use:
update yourtable
set email = replace(email, '.com', '.org')
You can also expand on this by checking the last 4 characters of the email value:
update yourtable
set email = replace(email, '.com', '.org')
where right(email, 4) = '.com'
However, the issue with replace()
is that .com
can be will in other locations in the email not just the last one. So you might want to use substring()
the following way:
update yourtable
set email = substring(email, 1, len(email) -4)+'.org'
where right(email, 4) = '.com';
See SQL Fiddle with Demo
Using substring()
will return the start of the email value, without the final .com
and then you concatenate the .org
to the end. This prevents the replacement of .com
elsewhere in the string.
Alternatively you could use stuff()
, which allows you to do both deleting and inserting at the same time:
update yourtable
set email = stuff(email, len(email) - 3, 4, '.org')
where right(email, 4) = '.com';
This will delete 4 characters at the position of the third character before the last one (which is the starting position of the final .com
) and insert .org
instead.
See SQL Fiddle with Demo for this method as well.
You could just use REPLACE
:
UPDATE myTable SET emailCol = REPLACE(emailCol, '.com', '.org')`.
But take into account an email address such as [email protected]
will be updated to [email protected]
.
If you want to be on a safer side, you should check for the last 4 characters using RIGHT
, and append .org
to the SUBSTRING
manually instead. Notice the usage of UPPER
to make the search for the .com
ending case insensitive.
UPDATE myTable
SET emailCol = SUBSTRING(emailCol, 1, LEN(emailCol)-4) + '.org'
WHERE UPPER(RIGHT(emailCol,4)) = '.COM';
See it working in this SQLFiddle.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With