How to extract numbers, English letters, and Chinese characters from strings in sqlserver to filter repeated characters

Some characters in a string are often used in recent projects, which is very tricky to handle. Now we can use the following method to easily process what you want from the string, encapsulate the following method into your own function, which is very convenient to call.

Note: The database used here must beSqlserverOh

-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
-- Test
Print DBO. get_number ('Ha abc123abc ')
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