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