SQL Server extracts Chinese characters, numbers, letters in content

--Remove the numbers

IF object_id (' DBO. Get_number2 ') is not NULL

DROP FUNCTION DBO. Get_number2

Go

The 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 (' hehe abc123abc ')

Go

--123

--------------------------------------------------------------------

--Take out English

IF object_id (' DBO. Get_str ') is not NULL

DROP FUNCTION DBO. Get_str

Go

The 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 (' hehe abc123abc ')

Go

--------------------------------------------------------------------

--Take out Chinese

IF object_id (' DBO. China_str ') is not NULL

DROP FUNCTION DBO. China_str

Go

The CREATE FUNCTION DBO. China_str (@S NVARCHAR (100))

RETURNS VARCHAR (100)

As

BEGIN

While PATINDEX ('%[^-seat]% ', @S) > 0

SET @S = STUFF (@s,patindex ('%[^ acridine-seat]% ', @S), 1,n ')

Return @S

End

Go

PRINT DBO. China_str (' hehe abc123abc ')

Go

SELECT * FROM (SELECT ' asdkg altar k unicom dl ' as col) TB WHERE col like N '%[acridine-]% '