SQL 如何替换sql字段值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14686204/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to replace sql field value
提问by user2039512
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
我真的不知道该怎么做，我对 SQL 有点陌生……在我的情况下我应该使用子字符串还是替换
I have in my DB filed called email, now I wanted to edited all email ending with email.com to email.org. What shall I do?
我在我的数据库中有一个名为 email 的文件，现在我想将所有以 email.com 结尾的电子邮件编辑为 email.org。我该怎么办？
回答by Taryn
It depends on what you need to do. You can use replacesince you want to replace the value:
这取决于您需要做什么。您可以使用，replace因为您要替换该值：
select replace(email, '.com', '.org')
from yourtable
Then to UPDATEyour table with the new ending, then you would use:
然后UPDATE用新的结尾到你的桌子，然后你会使用：
update yourtable
set email = replace(email, '.com', '.org')
You can also expand on this by checking the last 4 characters of the email value:
您还可以通过检查电子邮件值的最后 4 个字符来对此进行扩展：
update yourtable
set email = replace(email, '.com', '.org')
where right(email, 4) = '.com'
However, the issue with replace()is that .comcan be will in other locations in the email not just the last one. So you might want to use substring()the following way:
但是，问题replace()在于.com可以在电子邮件的其他位置而不仅仅是最后一个位置。因此，您可能希望使用substring()以下方式：
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 .comand then you concatenate the .orgto the end. This prevents the replacement of .comelsewhere in the string.
使用substring()将返回电子邮件值的开头，没有最后.com，然后将 连接.org到结尾。这可以防止替换.com字符串中的其他地方。
Alternatively you could use stuff(), which allows you to do both deleting and inserting at the same time:
或者，您可以使用stuff()，它允许您同时执行删除和插入操作：
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 .orginstead.
这将删除最后一个字符之前的第三个字符位置（即 final 的起始位置.com）处的 4 个字符并插入.org。
See SQL Fiddle with Demofor this method as well.
有关此方法，请参阅SQL Fiddle with Demo。
回答by Xavi López
You could just use REPLACE:
你可以使用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].
但请考虑[email protected]将更新为的电子邮件地址[email protected]。
If you want to be on a safer side, you should check for the last 4 characters using RIGHT, and append .orgto the SUBSTRINGmanually instead. Notice the usage of UPPERto make the search for the .comending case insensitive.
如果您想更安全一些，您应该使用RIGHT,检查最后 4 个字符，然后手动附加.org到SUBSTRING。注意UPPER使搜索.com结尾不区分大小写的用法。
UPDATE myTable
SET emailCol = SUBSTRING(emailCol, 1, LEN(emailCol)-4) + '.org'
WHERE UPPER(RIGHT(emailCol,4)) = '.COM';
See it working in this SQLFiddle.
看到它在这个SQLFiddle 中工作。
回答by veljasije
To avoid update names that contain .comlike [email protected]to [email protected], you can do this:
为避免更新包含.comlike [email protected]to 的名称[email protected]，您可以执行以下操作：
UPDATE Yourtable
SET Email = LEFT(@Email, LEN(@Email) - 4) + REPLACE(RIGHT(@Email, 4), '.com', '.org')
回答by SHUNMUGA RAJ PRABAKARAN
Try this query it ll change the records ends with .com
试试这个查询，它会改变以 .com 结尾的记录
UPDATE tablename SET email = replace(email, '.com', '.org') WHERE email LIKE '%.com';
