SQL 更新替换语句 [英] SQL Update Replace statement
本文介绍了SQL 更新替换语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要使用 REPLACE 编写 sql 更新语句.该字符串看起来像SE*88*000000001".我需要替换两个星号*"之间的数字.除了要替换的数字总是在两个星号之间之外,这里没有其他模式.在这种情况下可以使用通配符吗?
I need to write a sql update statement using REPLACE. The string looks like 'SE*88*000000001'. I need to replace the number between the two asterisks '*'. There is no pattern here other then that number to be replaced is always between two asterisks. Is it possible to use wild cards in this situation?
感谢您的帮助.
谢谢!
推荐答案
;
WITH RowSetToUpdate AS (
SELECT
acolumn,
Asterisk1Pos = CHARINDEX('*', acolumn),
Asterisk2Pos = CHARINDEX('*', acolumn, CHARINDEX('*', acolumn) + 1)
FROM atable
WHERE acolumn LIKE '%*%*%'
)
UPDATE RowSetToUpdate
SET acolumn = STUFF(
acolumn,
Asterisk1Pos + 1,
Asterisk2Pos - Asterisk1Pos - 1,
'replacement_string'
)
或者如果是要替换的特定数字,那就更简单了:
Or if it's a specific number that is to be replaced, then it would be even simpler:
UPDATE atable
SET acolumn = REPLACE(acolumn, '*88*', '*replacement_string')
WHERE acolumn LIKE '%*88*%'
这篇关于SQL 更新替换语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文