Six commonly used user-defined functions in SQL Server are shared.

Six commonly used user-defined functions in SQL Server are shared.

In daily applications, some values are often input based on actual needs, and these values cannot be directly used. Therefore, SQL usually performs some regular processing on field values. Here we collect (extract numbers, English letters, Chinese characters, filter repeated characters, and separate characters) to facilitate future query.

1. Determine whether the field value is Chinese

Copy codeThe Code is as follows:
-- SQL checks whether the field value is 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 ('bucket Forum KDL ')
-- Forum
Select dbo. fun_getCN ('asdkg; Comment KDL ')
-- Comment
Select dbo. fun_getCN ('asdkdl ')
-- Null

Ii. Extract numbers

Copy codeThe Code is as follows:
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
-- Test
Print dbo. GET_NUMBER ('Ha abc123abc ')
GO
-- 123

Iii. English Extraction

Copy codeThe Code is as follows:
-- 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
-- Test
Print dbo. GET_STR ('Ha abc123abc ')
GO

Iv. Extract Chinese Characters

Copy codeThe Code is as follows:
-- 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 ('% [^ A-seat] %', @ S), 1, n '')
RETURN @ S
END
GO
Print dbo. CHINA_STR ('Ha abc123abc ')
GO

5. Filter repeated fields (multiple methods)

Copy codeThe Code is as follows:
-- 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 = @ SPLIT + @ S
If right (@ S, 1) <> @ SPLIT
SET @ S = @ S + @ SPLIT
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, '') + @ 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 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 characters 2
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. Split field values based on specific strings

Copy codeThe Code is as follows:
IF OBJECT_ID ('dbo. SPLIT_STR ') IS NOT NULL
Drop function dbo. SPLIT_STR
GO
Create function dbo. SPLIT_STR (
@ S varchar (8000), a string containing multiple data items
@ INDEX int, -- position of the data item to be obtained
@ 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, @ S + @ SPLIT)> 0
SELECT @ INDEX = @ INDEX-1, @ S = STUFF (@ S, 1, CHARINDEX (@ SPLIT, @ S + @ SPLIT) + @ SPLITLEN ,'')
RETURN (ISNULL (LEFT (@ S, CHARINDEX (@ SPLIT, @ S + @ SPLIT)-1 ),''))
END
GO
Print dbo. SPLIT_STR ('aa | BB | CC', 2, '| ')
--
GO

SQL Server custom functions

Create a UDF:
Use Database Name
Go
Create function Name
(@ Pno int)
Returns int
As
Begin
Declare @ a int
If not exists (select * from person where pno = @ pno)
Set @ a =-1
Else
Set @ a = 1
Return @
End

Call the function:
Use Database Name
Go
Select dbo. Function Name (13250)

How to customize function variables in SQL Server

In SQL Server, functions can only use simple SQL statements and logical control statements. A complex stored procedure cannot be called, and execute sp_executesql or execute cannot be used in functions.

Why don't you create a stored procedure with two OUTPUT parameters?