Sharing of six commonly used custom functions in SQL Server

Time:2021-6-5

In daily applications, some values are often entered according to the actual needs, and these values can not be used directly, so SQL often carries out some routine processing on the field values. Here we collect the methods of extracting numbers, English, Chinese, filtering repeated characters and segmenting characters to facilitate future query and use.

1、 Determine whether the field value has Chinese character

--SQL determines whether the field value has Chinese characters
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 @[email protected][email protected]
 set @str=right(@str,len(@str)-1)  
 end  
 return @CN  
 end  
select dbo.fun_ Getcn ('asdkg forum KDL ')
--Forum
select dbo.fun_ Getcn ('asdkg forum KDL ')
--Forum
select dbo.fun_getCN('ASDKDL')
--Empty

2、 Extract numbers

IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN @S
END
GO
--Testing
PRINT DBO.GET_ Number ('abc123abc ')
GO
--123

3、 Extract English

--Extract English
IF OBJECT_ID('DBO.GET_STR') IS NOT NULL
DROP FUNCTION DBO.GET_STR
GO
CREATE FUNCTION DBO.GET_STR(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^a-z]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'')
END
RETURN @S
END
GO
--Testing
PRINT DBO.GET_ Str ('abc123abc ')
GO

4、 Extract Chinese

--Extract Chinese
IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL
DROP FUNCTION DBO.CHINA_STR
GO
CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
While patindex ('% [^ a-seat]%) ,@S ) > 0
Set @ s = stuff (@ s, patindex ('% [^ acridine]%) ,@S ),1,N'')
RETURN @S
END
GO
PRINT DBO.CHINA_ Str ('abc123abc ')
GO

5、 Filtering duplicate fields (multiple methods)

--Filter duplicate characters
IF OBJECT_ID('DBO.DISTINCT_STR') IS NOT NULL
DROP FUNCTION DBO.DISTINCT_STR
GO
CREATE FUNCTION DBO.DISTINCT_STR(@S NVARCHAR(100),@SPLIT VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)
IF LEFT(@S,1)<>@SPLIT
SET @S = @[email protected]
IF RIGHT(@S,1)<>@SPLIT
SET @S = @[email protected]
WHILE CHARINDEX(@SPLIT,@S)>0 AND LEN(@S)<>1
BEGIN
SET @INDEX = CHARINDEX(@SPLIT,@S)
SET @TEMP = LEFT(@S,CHARINDEX(@SPLIT,@S,@INDEX+LEN(@SPLIT)))
IF @NEW IS NULL
SET @NEW = ISNULL(@NEW,'')[email protected]
ELSE
SET @NEW = ISNULL(@NEW,'')+REPLACE(@TEMP,@SPLIT,'')[email protected]
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 RIGHT(LEFT(@NEW,LEN(@NEW)-1),LEN(LEFT(@NEW,LEN(@NEW)-1))-1)
END
GO
PRINT DBO.DISTINCT_STR('A,A,B,C,C,B,C,',',')
--A,B,C
GO
 
 
--------------------------------------------------------------------
--Filter duplicate characters2
IF OBJECT_ID('DBO.DISTINCT_STR2') IS NOT NULL
DROP FUNCTION DBO.DISTINCT_STR2
GO
CREATE FUNCTION DBO.DISTINCT_STR2(@S varchar(8000))
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)
WHILE LEN(@S)>0
BEGIN
SET @NEW=ISNULL(@NEW,'')+LEFT(@S,1)
SET @S=REPLACE(@S,LEFT(@S,1),'')
END
RETURN @NEW
END
GO
SELECT DBO.DISTINCT_STR2('AABCCD')
--ABCD
GO

6、 Splits field values based on a specific string

IF OBJECT_ID('DBO.SPLIT_STR') IS NOT NULL
DROP FUNCTION DBO.SPLIT_STR
GO
CREATE FUNCTION DBO.SPLIT_STR(
@S varchar (8000), -- string containing multiple data items
@Index int, -- the location of the data item to get
@Split varchar (10) -- data separator
)
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @SPLITLEN int
SELECT @SPLITLEN=LEN(@SPLIT+'A')-2
WHILE @INDEX>1 AND CHARINDEX(@SPLIT,@[email protected]PLIT)>0
SELECT @[email protected],@S=STUFF(@S,1,CHARINDEX(@SPLIT,@[email protected])[email protected],'')
RETURN(ISNULL(LEFT(@S,CHARINDEX(@SPLIT,@[email protected])-1),''))
END
GO
PRINT DBO.SPLIT_STR('AA|BB|CC',2,'|')
--
GO

Recommended Today

Knowledge Graph Reasoning for Joint Semantic and Data-Driven Path Representation

original Joint semantics and data-driven path representation for knowledge graph reasoning publishing Neurocomputing Volume 483 Issue C Apr 2022 pp 249–261 https://doi.org/10.1016/j.neucom.2022.02.011 declare The copyright belongs to the original author and the publisher. If there is any infringement, please contact to delete Summary Reasoning on large-scale knowledge graphs is of great significance for applications such […]