今天无意间看到一个sql处理数据的问题，记录下，说不定以后用得到
问题：
Id value 1 HelloWorld 2 NewArrivalsCareerClothing 3 CheckbookCoversCheckbookCovers 4 RetroHandbagsConvertibleBags 要更新成：
Id value 1 Hello World 2 New Arrivals Career Clothing 3 Checkbook Covers Checkbook Covers 4 Retro Handbags Convertible Bags 根据大写字母前插入一个空格。
答案：
1: CREATE FUNCTION dbo.RegexReplace 2: ( 3: @string VARCHAR(MAX), --被替换的字符串 4: @pattern VARCHAR(255), --替换模板 5: @replacestr VARCHAR(255), --替换后的字符串 6: @IgnoreCase INT = 0 --0区分大小写 1不区分大小写 7: ) 8: RETURNS VARCHAR(MAX) 9: AS 10: BEGIN 11: DECLARE @objRegex INT, @retstr VARCHAR(MAX) 12: 13: --创建对象 14: EXEC sp_OACreate 'VBScript.RegExp', @objRegex OUT 15: 16: --设置属性 17: EXEC sp_OASetProperty @objRegex, 'Pattern', @pattern 18: EXEC sp_OASetProperty @objRegex, 'IgnoreCase', @IgnoreCase 19: EXEC sp_OASetProperty @objRegex, 'Global', 1 20: 21: --执行 22: EXEC sp_OAMethod @objRegex, 'Replace', @retstr OUT, @string, @replacestr 23: 24: --释放 25: EXECUTE sp_OADestroy @objRegex 26: 27: RETURN @retstr 28: END 29: GO 30: 31: 32: --函数中用了OLE对象，所以需要使用sp_config将Ole Automation Procedures选项置为1 33: 34: EXEC sp_configure 'show advanced options', 1 35: RECONFIGURE 36: 37: EXEC sp_configure 'Ole Automation Procedures', 1 38: RECONFIGURE 39: 40: --针对这里，可以： 41: SELECT dbo.RegexReplace([value],' ([a-z])([A-Z])', '$1 $2',0) from tb 42: 43: /* 44: --------------------------------------- 45: Hello World 46: New Arrivals Career Clothing 47: Checkbook Covers Checkbook Covers 48: Retro Handbags Convertible Bags Zeros 49: 50: (4 行受影响) 51: */