Extract numbers, extract English letters, extract Chinese characters, filter repeated characters, and other SQL Functions

-- Extract the number if object_id ('dbo. get_number2 ') is not nulldrop function DBO. get_number2gocreate function DBO. get_number2 (@ s varchar (100) returns varchar (100) asbeginwhile patindex ('% [^ 0-9] %', @ s)> 0 beginset @ s = stuff (@ s, patindex ('% [^ 0-9] %', @ s), 1, '') endreturn @ sendgo -- test print DBO. get_number2 ('Ha abc123abc') go--123 ------------------------------------------------------------------ extract the English if object_id ('dbo. get_str ') is not nulldrop function DBO. get_strgocreate function DBO. get_str (@ s varchar (100) returns varchar (100) asbeginwhile patindex ('% [^ A-Z] %', @ s)> 0 beginset @ s = stuff (@ s, patindex ('% [^ A-Z] %', @ s), 1, '') endreturn @ sendgo -- test print DBO. get_str ('Ha abc123abc') Go -------------------------------------------------------------------- extract Chinese if object_id ('dbo. china_str ') is not nulldrop function DBO. china_strgocreate function DBO. china_str (@ s nvarchar (100) returns varchar (100) asbeginwhile patindex ('% [^ A-seat] %', @ s)> 0 set @ s = stuff (@ s, patindex ('% [^ A-seat] %', @ s), 1, n') return @ sendgoprint DBO. china_str ('Ha abc123abc') Go ---------------------------------------------------------------------- filter duplicate characters if object_id ('dbo. distinct_str ') is not nulldrop function DBO. distinct_strgocreate function DBO. distinct_str (@ s nvarchar (100), @ split varchar (50) returns varchar (100) asbeginif @ s is null return (null) Declare @ new varchar (50 ), @ index int, @ temp varchar (50) if left (@ s, 1) <> @ splitset @ s = @ split + @ SIF right (@ s, 1) <> @ splitset @ s = @ s + @ splitwhile charindex (@ split, @ s)> 0 and Len (@ s) <> 1 beginset @ Index = charindex (@ split, @ s) set @ temp = left (@ s, charindex (@ split, @ s, @ index + Len (@ split ))) if @ new is null set @ new = isnull (@ new, '') + @ tempelseset @ new = isnull (@ new,'') + Replace (@ temp, @ split, '') + @ splitwhile charindex (@ temp, @ s)> 0beginset @ s = stuff (@ s, charindex (@ temp, @ s) + Len (@ split ), charindex (@ split, @ s, charindex (@ temp, @ s) + Len (@ split)-charindex (@ temp, @ s ),'') endendreturn right (left (@ new, Len (@ new)-1), Len (left (@ new, Len (@ new)-1)-1) endgoprint DBO. distinct_str ('a, A, B, C, C, B, C, ') -- A, B, CGO unique filter duplicate characters 2if object_id ('dbo. distinct_str2 ') is not nulldrop function DBO. distinct_str2gocreate function DBO. distinct_str2 (@ s varchar (8000) returns varchar (100) asbeginif @ s is null return (null) Declare @ new varchar (50), @ index int, @ temp varchar (50) while Len (@ s)> 0 beginset @ new = isnull (@ new, '') + Left (@ s, 1) set @ s = Replace (@ s, left (@ s, 1), '') endreturn @ newendgoselect DBO. distinct_str2 ('aabccd ') -- ABCDGO--------------------------------------------------------------------IF object_id ('dbo. split_str ') is not nulldrop function DBO. split_strgocreate function DBO. split_str (@ s varchar (8000), -- string containing multiple data items @ index int, -- location of the data item to be obtained @ split varchar (10) -- Data Separator) returns varchar (100) asbeginif @ s is null return (null) Declare @ splitlen intselect @ 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), '') endgoprint DBO. split_str ('aa | BB | CC', 2, '|') -- go