SQL Server Regular Expression replacement instance sharing-[Leaf]

-- ========================================= -- Title: application Instance of SQL Server Regular Expression replacement -- Author: maco_wang -- Time: 2012-03-24 -- Note: Regular Expression replacement in MS-SQL server -- Supplement: activities supported by the csdn community -- ==================================== =====/* assume that the test data is: col ---------------------- the result to be obtained by cola (replace the consecutive letters in the field '. '): Col -------------- 192.168.0.10110.176.98.121112.19.11 0.4580.230.120.224121.21.30.90 */-- test data: If object_id ('[TB]') is not null drop table [TB] Create Table [TB] (COL varchar (100 )) insert into [TB] Select 'hangzhou' Union allselect '10hh176bag98job121zero 'Union allselect' Union allselect 'a80abab230pp120quand' Union allselect 'hangzhou' -- this example can be used in SQL Server 2005. -- Normal thinking -- A) cursor loop truncation (Omitted) -- B) custom function gocreate function [DBO]. [fn_replace] (@ STR nvarchar (100) returns varchar (100) asbegin while patindex ('% [A-Z] %', @ Str)> 0 begin set @ STR = stuff (@ STR, patindex ('% [A-Z] %', @ Str), 1, N '. '); end while (charindex ('.. ', @ Str) <> 0) Begin set @ STR = Replace (@ STR ,'.. ','. ') end if (left (@ STR, 1) = '. ') set @ STR = right (@ STR, Len (@ Str)-1) if (right (@ STR, 1) = '. ') set @ STR = left (@ STR, Len (@ Str)-1) retur N @ strendgoselect DBO. [fn_replace] (COL) as col from [TB]/* Col ------------- begin */-- c) Other methods. Here I will mainly introduce regular expression replacement, because patindex and like support a very small number of regular expressions-regular replacement-enable xp_mongoshell-if not, the following message is displayed: SQL Server blocked access to procedure 'xp _ Your shell' gosp _ configure 'show advanced options', 1goreconfiguregosp_configure 'xp _ Your shell', 1goreconfigure -- enable sp_oacreate -- do not enable will prompt: S QL Server blocked access to procedure 'sys. sp_oacreate 'gosp _ configure 'show advanced options', 1; goreconfigure; gosp_configure 'ole automation procedures ', 1; goreconfigure; go -- Create Function [DBO]. [regexreplace] (@ source varchar (8000), -- string @ Regexp varchar (500), -- Regular Expression @ replace varchar (500 ), -- replace value @ globalreplace bit = 0, -- whether to globally replace @ ignorecase bit = 0 -- whether to ignore case sensitivity) returns varchar (1000) asbegin d Eclare @ HR int declare @ objregexp int declare @ result varchar (5000) exec @ hR = sp_oacreate 'vbscript. regexp ', @ objregexp output if @ HR <> 0 begin exec @ hR = sp_oadestroy @ objregexp return NULL end exec @ hR = sp_oasetproperty @ objregexp, 'pattern ', @ Regexp if @ HR <> 0 begin exec @ hR = sp_oadestroy @ objregexp return NULL end exec @ hR = sp_oasetproperty @ objregexp, 'global', @ globalreplace if @ HR <> 0 begin exec @ hR = sp_oadestroy @ objregexp return NULL end exec @ hR = sp_oasetproperty @ objregexp, 'ignorecasase ', @ ignorecase if @ HR <> 0 begin exec @ hR = sp_oadestroy @ objregexp return NULL end exec @ hR = sp_oamethod @ objregexp, 'replace ', @ result output, @ source, @ replace if @ HR <> 0 begin exec @ hR = sp_oadestroy @ objregexp return NULL end exec @ hR = sp_oadestroy @ objregexp if @ HR <> 0 begin retu Rn null end/* Comment A -- while (charindex ('.. ', @ result) <> 0) -- begin -- set @ result = Replace (@ result ,'.. ','. ') -- end -- If (left (@ result, 1) = '. ') -- set @ result = right (@ result, Len (@ result)-1) -- If (right (@ result, 1) = '. ') -- set @ result = left (@ result, Len (@ result)-1) */return @ resultend -- view the result goselect DBO. regexreplace (COL, '[A-Z]', '. ', 192) as col from [TB]/* Col... 168 .. 0... 101 .... 10 .. 176... 98. .. 121 ......... 112. 19 .. 110 .... 45. 80 .... 230 .... 120 .. 224121 .... 21 ..... 30 .... 90 */-- replace the letter '. 'the result is not the same as the expected result-you need to uncomment "comment a" in the function to ensure that the result is the same, it is better to directly use the above user-defined functions-is there any other way? -- If the character length in the TB table is 100, modify the Regular Expression and replace it '. 'Try 'select DBO. regexreplace (COL, '[A-Z] {1,100 }','. ', 1, 0) as col from [TB]/* Col ---------------- 192.168.0.101.10.176.98.121 .. 112.19.110.45.80.230.120.224121.21.30.90 */-- the results are still different -- the beginning and end are redundant '. '-- you don't want to use left, right, or substring to intercept the regular expression. Can you fix it? -- Modify select Col = DBO. regexreplace (DBO. regexreplace (COL, '[A-Z] {1,100 }','. ', 1, 0),' ^ \. {1} | \. {1} $ ', '',) from [TB]/* Col -------------- regular */-- this example is relatively simple for regular expressions, here is just an introduction.