–【提取中文】
IF OBJECT_ID’dbo.fun_getCN’) IS NOT NULL
DROP FUNCTION dbo.fun_getCN
GO
create function dbo.fun_getCN@str varchar4000))
returns varchar4000)
as
begin
declare @word nchar1),@CN varchar4000)
set @CN=”
while len@str)>0
begin
set @word=left@str,1)
if unicode@word) between 19968 and 40869
set @CN=@CN+@word
set @str=right@str,len@str)-1)
end
return @CN
end
GO
SELECT DBO.fun_getCN’turing我666QQ中国AK123′)
–【提取中文】
IF OBJECT_ID’DBO.get_Chinese’) IS NOT NULL
DROP FUNCTION DBO.get_Chinese
GO
CREATE FUNCTION DBO.get_Chinese@S NVARCHAR100))
RETURNS VARCHAR100)
AS
BEGIN
WHILE PATINDEX’%[^吖-座]%’,@S) > 0
SET @S = STUFF@S,PATINDEX’%[^吖-座]%’,@S),1,N”)
RETURN @S
END
GO
SELECT DBO.get_Chinese’turing我666QQ中国AK123′)
–【提取数字】
IF OBJECT_ID’dbo.GET_NUMBER’) IS NOT NULL
DROP FUNCTION dbo.GET_NUMBER
GO
CREATE FUNCTION dbo.GET_NUMBER@S VARCHAR100))
RETURNS VARCHAR100)
AS
BEGIN
WHILE PATINDEX’%[^0-9]%’,@S) > 0
BEGIN
set @s=stuff@s,patindex’%[^0-9]%’,@s),1,”)
END
RETURN @S
END
GO
SELECT DBO.GET_NUMBER’turing我666QQ中国AK123′)
–【提取英文】
IF OBJECT_ID’DBO.get_English’) IS NOT NULL
DROP FUNCTION DBO.get_English
GO
CREATE FUNCTION DBO.get_English@S VARCHAR100))
RETURNS VARCHAR100)
AS
BEGIN
WHILE PATINDEX’%[^a-z]%’,@S) > 0
BEGIN
set @s=stuff@s,patindex’%[^a-z]%’,@s),1,”)
END
RETURN @S
END
GO
SELECT DBO.get_English’turing我666QQ中国AK123′)