Use regular expressions to replace strings and Server Regular Expressions in SQL server

Use regular expressions to replace strings and Server Regular Expressions in SQL server

Create a regular expression replacement function and use the OLE object. The following is the function code:

[SQL]View plain copy

1. -- If yes, delete the original function.
2. IF OBJECT_ID (N 'dbo. RegexReplace ') IS NOT NULL
3. Drop function dbo. RegexReplace
4. GO
5. -- Start to create a regular expression replacement function
6. Create function dbo. RegexReplace
7. (
8. @ String VARCHAR (MAX), -- replaced string
9. @ Pattern VARCHAR (255), -- replace Template
10. @ Replacestr VARCHAR (255), -- replaced string
11. @ IgnoreCase INT = 0 -- 0 case sensitive 1 case insensitive
12. )
13. Returns varchar (8000)
14. AS
15. BEGIN
16. DECLARE @ objRegex INT, @ retstr VARCHAR (8000)
17. -- Create an object
18. EXEC sp_OACreate 'vbscript. RegExp ', @ objRegex OUT
19. -- Set attributes
20. EXEC sp_OASetProperty @ objRegex, 'pattern', @ Pattern
21. EXEC sp_OASetProperty @ objRegex, 'ignorecase', @ IgnoreCase
22. EXEC sp_OASetProperty @ objRegex, 'global', 1
23. -- Execute
24. EXEC sp_OAMethod @ objRegex, 'replace ', @ retstr OUT, @ string, @ replacestr
25. -- Release
26. EXECUTE sp_OADestroy @ objRegex
27. RETURN @ retstr
28. END
29. GO
30. -- Set Ole Automation Procedures to 1 to ensure normal operation.
31. EXEC sp_configure 'show advanced options', 1
32. RECONFIGURE WITH OVERRIDE
33. EXEC sp_configure 'ole Automation Procedures ', 1
34. RECONFIGURE WITH OVERRIDE

Copy the preceding code and run it in SQL.

[SQL]View plain copy

1. -- 2. Rename
2. SELECT dbo. regexReplace ('John Smith ',' ([a-z] +) \ s ([a-z] +) ',' $2, $ 1', 1)
3. /*
4. --------------------------------------
5. Smith, John
6. */