今天无意间看到一个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: */