SQL Server extracts a number, extracts English, extracts Chinese SQL statements _mssql

--sql Determine if the field value has a Chinese create function fun_getcn (@str nvarchar (4000)) returns nvarchar (4000) as BEGIN declare @word NCHAR (1), @CN nvarchar (4000) Set @CN = ' while Len (@str) >0 begin set @word =left (@str, 1) if Unicode (@word)  Between 19968 and 19968+20901 set @CN = @CN + @word set @str =right (@str, Len (@str)-1) End return @CN End Select DBO.FUN_GETCN (' asdkg forum KDL ')--Forum Select DBO.FUN_GETCN (' asdkg altar ')--theory KDL Select Altar (' DBO.FUN_GETCN ')--empty---------- -----------------------------Extract the number IF object_id (' DBO. Get_number2 ') is a not NULL DROP FUNCTION DBO. Get_number2 go CREATE FUNCTION DBO. Get_number2 (@S VARCHAR) RETURNS VARCHAR (m) as BEGIN while PATINDEX ('%[^0-9]% ', @S) > 0 BEGIN set @s=stuff (@s,pati Ndex ('%[^0-9]% ', @s), 1, ') End return @s the end go--Test PRINT DBO. Get_number (' Ha abc123abc ') go--123----------------------------------------------------------------------extract English IF OBJECT_ID (' DBO. Get_str ') is a not NULL DROP FUNCTION DBO. Get_str Go CREATE FUNCTION DBO. Get_str (@S VARCHAR) RETURNS VARCHAR (m) as BEGIN while PATINDEX ('%[^a-z]% ', @S) > 0 BEGIN set @s=stuff (@s,patindex ('%[^a-z]% ', @s), 1, ' "] end return @s end go--Test PRINT DBO. Get_str (' Ha abc123abc ') go----------------------------------------------------------------------Extract Chinese IF object_id ( ' DBO. China_str ') is a not NULL DROP FUNCTION DBO. China_str go CREATE FUNCTION DBO. China_str (@S NVARCHAR) RETURNS VARCHAR (m) as BEGIN while PATINDEX ('%[^ acridine-seat]% ', @S) > 0 SET @S = STUFF (@s,patindex ( '%[^-]% ', @S), 1,n ') return @S the end go PRINT DBO. China_str (' hehe abc123abc ') Go----------------------------------------------------------------------Filter repeat character IF OBJECT _id (' DBO. Distinct_str ') is a not NULL DROP FUNCTION DBO. Distinct_str go CREATE FUNCTION DBO. Distinct_str (@S NVARCHAR), @SPLIT VARCHAR) RETURNS VARCHAR (m) as BEGIN IF @S is null return (NULL) DECLARE @NEW VA Rchar (@INDEX INT, @TEMP VARCHAR () IF left (@s,1) <> @SPLIT SET @S = @SPLIT +@s IF Right (@s,1) <> @SPLIT SET @S= @s+ @SPLIT while CHARINDEX (@SPLIT, @S) >0 and LEN (@S) <>1 the BEGIN set @INDEX = CHARINDEX (@SPLIT, @S) Set @TEMP = Left ( @s,charindex (@SPLIT, @S, @INDEX +len (@SPLIT))) IF @NEW is NULL SET @NEW = ISNULL (@NEW, "") + @TEMP ELSE SET @NEW = ISNULL (@NEW, ') +replace (@TEMP, @SPLIT, ') + @SPLIT while CHARINDEX (@TEMP, @S) >0 BEGIN SET @s=stuff (@s,charindex (@TEMP, @S) +len (@ SPLIT), CHARINDEX (@SPLIT, @s,charindex (@TEMP, @S) +len (@SPLIT))-charindex (@TEMP, @S), "" "End-end return (@NEW , Len (@NEW)-1), Len (@NEW, Len (@NEW)-1)-1) End go PRINT DBO. Distinct_str (' A,a,b,c,c,b,c, ', ', ')--a,b,c go------------------------------------------------------------------- --Filter Repeat character 2 IF object_id (' DBO. Distinct_str2 ') is a not NULL DROP FUNCTION DBO. DISTINCT_STR2 go CREATE FUNCTION DBO. DISTINCT_STR2 (@S varchar (8000)) RETURNS varchar (m) as BEGIN IF @S is NULL return (NULL) DECLARE @NEW varchar (@INDEX I NT, @TEMP VARCHAR () while LEN (@S) >0 BEGIN SET @NEW =isnull (@NEW, ') +left (@s,1) SET @s=replace (@s,left (@s,1), "") END return @NEW the end go SELECT DBO. DISTINCT_STR2 (' AABCCD ')--abcd go--------------------------------------------------------------------IF object_id (' DBO. Split_str ') is a not NULL DROP FUNCTION DBO. Split_str go CREATE FUNCTION DBO. SPLIT_STR (@S varchar (8000),--string @INDEX int that contains multiple items of data--the position of the data item to get @SPLIT varchar (10)--Data separator RETURNS varchar (@S) as BEGIN IF is null return (NULL) DECLARE @SPLITLEN int SELECT @SPLITLEN =len (@SPLIT + ' A ")-2 while @INDEX >1 and C
Harindex (@SPLIT, @s+ @SPLIT) >0 SELECT @INDEX = @INDEX -1,@s=stuff (@s,1,charindex (@SPLIT, @s+ @SPLIT) + @SPLITLEN, "") Return (ISNULL (@s,charindex (@SPLIT, @s+ @SPLIT)-1), "") End go PRINT DBO. Split_str (' aa| Db:
CC ', 2, ' | ') --Go