English | 正體中文 | 简体中文

SQL replace


范例资料表 : Orders (以下范例皆用此表当作资料来源)
OrderNo CustomerName Shipped SalePrice Quantity
20101213095 John True 13500 10
20100620135 Jimmy False 25096 22
20100620392 Vincent False 7200 5
20100530006 Tyler True 92500 50
20100515029 Alan False 100 50


Example
替换掉指定栏位里的部份字串

Sytax : SELECT REPLACE(column_name, match_string, replace_string ) FROM table_name
SQL
SELECT REPLACE(OrderNo, '2010', '2012') FROM Orders

会输出结果
OrderNo
20121213095
20120620135
20120620392
20120530006
20120515029


Example
替换掉所有资料列里指定栏位的部份字串

Sytax : UPDATE table_name SET column_name = REPLACE(column_name, match_string, replace_string)
SQL
UPDATE Orders SET CustomerName = REPLACE(CustomerName, 'n', 'm')

会输出结果
OrderNo CustomerName Shipped SalePrice Quantity
20101213095 Johm True 13500 10
20100620135 Jimmy False 25096 22
20100620392 Vimcemt False 7200 5
20100530006 Tyler True 92500 50
20100515029 Alam False 100 50

© 2012 PureExample.com contact