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?
14 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 yourtableThen 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';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.
2You could just use REPLACE:
UPDATE myTable SET emailCol = REPLACE(emailCol, '.com', '.org')`.But take into account an email address such as will be updated to .
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.
To avoid update names that contain .com like to , you can do this:
UPDATE Yourtable
SET Email = LEFT(@Email, LEN(@Email) - 4) + REPLACE(RIGHT(@Email, 4), '.com', '.org')Try this query it ll change the records ends with .com
UPDATE tablename SET email = replace(email, '.com', '.org') WHERE email LIKE '%.com';
