SQL Server Regular Expression matching instance sharing-[Leaf]

-- ========================================= -- Title: SQL Server Regular Expression matching for Application Instances -- Author: maco_wang -- Time: 2012-03-25 -- Description: regular match in MS-SQL server -- ============================================== =/* assume that the test data is: col ---------- a B d c ea a B c dB B c d EE u g h wo A K d W1) to get a row without duplicate letters, you want to get the following results: col -------------- a B c d EE u g h wo P K N W2) GET A and D at the same time, and there is only 0 or 1 letter between A and D, the expected result is col ---------- a B D C EO A K d w */-- test data if object_id ('[TB]') is not null. Drop table [TB] Create Table [TB] (COL varchar (10 )) insert into [TB] Select 'a B d c E' Union allselect 'a B c d' Union allselect 'B B c d E' Union allselect 'e u g h W' union allselect 'o a k d W' select * from [TB] -- this example can be applied in SQL Server 2000. Gocreate function DBO. regexmatch (@ pattern varchar (2000), @ matchstring varchar (8000) returns INTAS begin declare @ objregexexp int declare @ strerrormessage varchar (255) Declare @ HR int, @ match bit exec @ hR = sp_oacreate 'vbscript. regexp ', @ objregexexp out if @ hR = 0 exec @ hR = sp_oasetproperty @ objregexexp, 'pattern', @ pattern if @ hR = 0 exec @ hR = sp_oasetproperty @ objregexexp, 'ignorecase', 1 If @ hR = 0 exec @ hR = sp_oamethod @ objregexexp, 'test', @ match out, @ matchstring if @ HR <> 0 begin return NULL end exec sp_oadestroy @ objregexexp return @ matchendgo -- 1) get a row without repeated letters-the normal idea is to split the lines by spaces, deduplicate them, merge them, and finally determine the length (omitted) -- using regular expressions makes it easy to select Col from [TB] Where DBO. regexmatch ('^. *? ([A-Z]) [] \ 1 .*? $ ', Col) = 0/* Col ---------- a B D C EE u g h wo A K d w */-- 2) Get that both A and D exist, and the interval between A and D is less than or equal to a letter-normal idea: Select Col from [TB] Where charindex ('A', col)> 0and charindex ('D', col)> 0 and ABS (charindex ('A', col)-charindex ('D', col )) <5/* Col ---------- a B D C EO A K d w */-- Regular processing select Col from [TB] Where DBO. regexmatch ('. * A [A-Z] {1, 3} D. * ', col) = 1/* Col ---------- a B D C EO A K d w */-- the regular expression here is not fully considered. What if D is before? -- Modify select Col from [TB] Where DBO. regexmatch ('. * d [A-Z] {1, 3}. * |. * A [A-Z] {1, 3} D. * ', col) = 1/* Col ---------- a B D C EO A K d w */