IF OBJECT_ID ('dbo. GET_NUMBER ') IS NOT NULL
Drop function dbo. GET_NUMBER
GO
Create function dbo. GET_NUMBER (@ 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 ('haha ABC-12, 3abc ')
GO
-- 123
--------------------------------------------------------------------
-- 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
--------------------------------------------------------------------
-- 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
--------------------------------------------------------------------
-- 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 --------------------------------------------------------------------
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 |
