向高手请教

Question

有一个字段要统一按照下面的变化
http://bbs.csdn.net/2013/27/zjshfbg.ts变更为http://bbs.csdn.net/2013/27/zjshfbg/zjshfbg.ts
高手

Answer 1

what

Answer 2

REPLACE('http://bbs.csdn.net/2013/27/zjshfbg.ts','zjshfbg.ts','zjshfbg/zjshfbg.ts')

Answer 3

replace(a,'zjshfbg.ts','zjshfbg/zjshfbg.ts')  a为字段

Answer 4

首先感谢大家的答复，这个字段里有几万条需要处理的链接地址，目前只有文件后缀是一样的其他都有可能不一样，是否可以做批量处理。http://bbs.csdn.net/2013/27/zjshfbg（无规则）.ts变更http://bbs.csdn.net/2013/27/zjshfbg（无规则）/zjshfbg（无规则）.ts

Answer 5

不能单纯的replace的，我个理解是用parindex先获取.和最后一个/（这一步可以用reserve()来实现）之间的这部分，才能开始replace

Answer 6

有点复杂，你要慢慢看DECLARE @a VARCHAR(256)
SET @a = 'http://bbs.csdn.net/2013/27/zjshfbg.ts'SELECT  REVERSE(REPLACE(REVERSE(@a),
                        SUBSTRING(REVERSE(@a),
                                  PATINDEX('%.%', REVERSE(@a)) + 1,
                                  PATINDEX('%/%', REVERSE(@a))
                                  - PATINDEX('%.%', REVERSE(@a)) - 1),
                        SUBSTRING(REVERSE(@a),
                                  PATINDEX('%.%', REVERSE(@a)) + 1,
                                  PATINDEX('%/%', REVERSE(@a))
                                  - PATINDEX('%.%', REVERSE(@a)) - 1) + '/'
                        + SUBSTRING(REVERSE(@a),
                                    PATINDEX('%.%', REVERSE(@a)) + 1,
                                    PATINDEX('%/%', REVERSE(@a))
                                    - PATINDEX('%.%', REVERSE(@a)) - 1)))
                                    /*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
http://bbs.csdn.net/2013/27/zjshfbg/zjshfbg.ts
*/