SQL server extracts Chinese characters, numbers, and letters from the content

-- Retrieve 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

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

-- Retrieve 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

SELECT * FROM (select 'asdkg; Comment K Unicom DL 'as col) tb where col like n' % [A-Snapshot] %'