MySQL 从值中删除部分文本的 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14527859/
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
SQL to remove partial text from value
提问by 1.21 gigawatts
How would I write the SQL to remove some text from the value of records if it exists.
如果存在，我将如何编写 SQL 以从记录的值中删除一些文本。
Version Column data
------------------
WEB 1.0.1
WEB 1.0.1
1.0.2
1.0.2
I would like to update all records that contain "WEB " and remove that text but leave the rest, "1.0.1".
我想更新包含“WEB”的所有记录并删除该文本但保留其余部分“1.0.1”。
So far I have Select * from table.
到目前为止我有Select * from table。
Database is MySQL 5.5.25
数据库是 MySQL 5.5.25
回答by Brian Webster
The REPLACEfeature of MySQL, SQL Server, and PostGreswill remove all occurrences of WEBwith a blank.
该REPLACE特征的MySQL，SQL服务器和Postgres的将删除所有出现的WEB一个空白。
Selecting
选择
SELECT REPLACE(Version, 'WEB ', '') FROM MyTable
Updating
更新中
UPDATE MyTable SET Version = REPLACE(Version, 'WEB ', '')
or
或者
UPDATE MyTable SET Version = REPLACE(Version, 'WEB ', '') WHERE Version LIKE '%WEB %'
Reference
参考
回答by jdennison
In postgres this would be:
在 postgres 中，这将是：
select substring(Version from 5 for 3)
from table
where Version like '%WEB%'
回答by Darren Kopp
UPDATE Table
SET Version = Replace(Version, 'Web ','')
WHERE Version LIKE 'WEB %'
回答by Art
Here's another example that should work in any SQL as functions used are ANSI SQL standard. This example assumes that there is a whitespace between word 'WEB' and first digit. But it can be improved. I think this is more generic approach then hardcoding start and end positions for subtr:
这是另一个应该在任何 SQL 中工作的示例，因为使用的函数是 ANSI SQL 标准。此示例假定单词 'WEB' 和第一个数字之间有一个空格。但它可以改进。我认为这是比 subtr 硬编码开始和结束位置更通用的方法：
SELECT TRIM(SUBSTR('WEB 1.0.1', INSTR('WEB 1.0.1', ' ') ) ) as version
FROM dual;
SQL>
VERSION
-------
1.0.1
