Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to replace SQL field value

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?

like image 671
user2039512 Avatar asked Feb 04 '13 11:02

user2039512


People also ask

How do you change a value in a database?

The following are the syntax of REPLACE statement in MySQL: REPLACE [INTO] table_name(column_list) VALUES(value_list);

How do you UPDATE a string in SQL?

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!


2 Answers

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.

like image 104
Taryn Avatar answered Oct 13 '22 05:10

Taryn


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.

like image 25
Xavi López Avatar answered Oct 13 '22 04:10

Xavi López