6 Custom Function shares commonly used in SQL Server _mssql

In the daily application, often according to the actual requirements of the input of some values, and these values can not be used directly, so in SQL often on the field value of some conventional processing. Collected here (extract the number, English, Chinese, filter repeating characters, split the character method), convenient for future query use.

First, judge whether the field value has Chinese

--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 theory Altar KDL ')--
theory altar
Select DBO.FUN_GETCN (' ASDKDL ')--
null

Ii. Extraction of digital

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,patindex ('%[^0-9]% ', @s), 1, ') End return @s end go
--Test
PRINT DBO. Get_number (' hehe abc123abc ')
go
--123

Iii. Extraction of English

--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
-- Tests the
PRINT DBO. Get_str (' hehe abc123abc ') go

Iv. Extraction of Chinese

--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 ('%[^ acridine-seat]% ', @S), 1,n ') return to @S end go
PRINT DBO. China_str (' hehe abc123abc ') go

V. Filter repeating fields (multiple methods)

--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

Dividing field values according to a specific string

IF object_id (' DBO. Split_str ') is a not NULL
DROP FUNCTION DBO. Split_str
go
CREATE FUNCTION DBO. SPLIT_STR (
@S varchar (8000), a   string containing multiple data items
@INDEX int, the position of the       data item to get
@SPLIT varchar   -Data delimiter
)
RETURNS VARCHAR as
BEGIN
IF @S is null return (NULL)
DECLARE @SPLITLEN int
SELECT @SPLITLEN =len (@SPLIT + ' A ')-2
While @INDEX >1 and CHARINDEX (@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