SQL Server garbled processing (ASCII)

CREATE FUNCTION [dbo].  [Regexreplace]      (@string varchar (MAX),--substituted string @pattern varchar (255),--Replacement template @replacestr varchar (255),--replaced string @IgnoreCase int = 0--0 Case-sensitive 1 is case-insensitive) RETURNS varchar (8000) as BEGIN DECLARE @objRegex INT, @retstr varchar (  8000)--Create object exec sp_oacreate ' VBScript.RegExp ', @objRegex out--set properties exec sp_OASetProperty @objRegex,  ' Pattern ', @pattern exec sp_oasetproperty @objRegex, ' IgnoreCase ', @IgnoreCase exec sp_OASetProperty @objRegex, ' Global ', 1-exec sp_OAMethod @objRegex, ' Replace ', @retstr out, @string, @replacestr--release execut E sp_OADestroy @objRegex RETURN @retstr END----Ensure normal operation, you need to set the OLE Automation procedures option to 1--exec sp_configure ' Show advanced Options ', 1--reconfigure with OVERRIDE--exec sp_configure ' Ole Automation procedures ', 1--RECONFI Gure with override--match string ' \b String '--match html/xml ' \<[^<>]*\> '--match ASCII code ' [\x01-\x3f] ' Note here all ASCII codes are filteredSpecial symbols included? () etc. can be adjusted on Demand,--SELECT ASCII (') to find the ASCII value of the special symbol--select CHAR (0x03) is used to display the special symbol go

CREATE FUNCTION [dbo]. [Remove garbled] (@str NVARCHAR (100)) RETURNS VARCHAR as BEGIN RETURN replace (replace, replace (replace (replace replace (REPL ACE (replace (replace (replace) (replace (replace (replace (replace) (                                            Replace (replace (replace) (replace (replace (@str,                                            NCHAR (0x00), "), NCHAR (0x01),"),                                            NCHAR (0x02), ""), NCHAR (0x03), '),                                            NCHAR (0x04), '), NCHAR (0x05), '),                                            NCHAR (0x06), '), NCHAR (0x07), '),                       NCHAR (0x08), ""), NCHAR (0x0b), '),                     NCHAR (0x0c), ""), NCHAR (0x0e), '),                                            NCHAR (0x0f), ""), NCHAR (0x10), '), NCHAR (0x11), '),                                            NCHAR (0x12), ""), NCHAR (0x13), '),                                            NCHAR (0x14), '), NCHAR (0x15), '),                                            NCHAR (0x16), ""), NCHAR (0x17), '),                                            NCHAR (0x18), ""), NCHAR (0x19), '),                                            NCHAR (0x1A), ""), NCHAR (0x1B), '), NCHAR (0x1C), ""), NCHAR (0x1D), '), NCHAR (             0x1E), "),                               NCHAR (0x1F), '), NCHAR (Ten), '), NCHAR (32), ')    , NCHAR (+), '), NCHAR (9), '); END; GO

The above is two ways to remove garbled data can be changed according to the needs of their own, the first take advantage of the increment expression of the matching interval and need to open the additional function (sp_OACreate function is quite powerful to open SQL Server Call Web Service), the second is for the case, Here is the test script

SELECT dbo. Regexreplace (' Chengdu Wuhou 0H 倎 x (4 Putuo Street Ganquanlu 241 Lane 25th # 302 Room               0 ', ' [\x01-\x20] ', ', 0); SELECT dbo. Garbled (' Chengdu Wuhou 0H 倎 x (4 Putuo Street Ganquanlu 241 Lane 25th, 302 room               0 '); Select ASCII (") Select CHAR (0x03)

In addition, an excerpt from an online SQL Server basic Regular Expression usage example

CREATE FUNCTION [dbo]. [Extracting Chinese characters] (@S NVARCHAR (100)) RETURNS VARCHAR as    BEGIN while        PATINDEX ('%[^-seat]% ', @S) > 0            SET @S = STUFF (@S, PATINDEX ('%[^-block]% ', @ S), 1,                           N ");        RETURN @S;    END; Gocreate FUNCTION [dbo]. [Extract numbers] (@S VARCHAR (100)) RETURNS VARCHAR (+)    as BEGIN        while PATINDEX ('%[^0-9]% ', @S) > 0            BEGIN                SET @S = STUFF (@S, PATINDEX ('% [^0-9]% ', @S),                               1, ');            END;        RETURN @S;    END; Gogocreate FUNCTION [dbo]. [Extract letters] (@S VARCHAR (100)) RETURNS VARCHAR as    begin        while PATINDEX ('%[^a-z]% ', @S) > 0            BEGIN                SET @S = STUFF (@S, PATINDEX ('%[^a-z]% ', @S),                               1, ');            END;        RETURN @S;    END;

* Regular expression notation ^ in SQL Server does not match the first character outside [], this is different from the general regular expression, the general regular expression ^[0-9] means match the first is a number 0-9, [^0-9] match is not 0-9 of the number

SQL Server garbled processing (ASCII)