--【提取中文】
IF OBJECT_ID('dbo.fun_getCN') IS NOT NULL
DROP FUNCTION dbo.fun_getCN
GO
create function dbo.fun_getCN(@str varchar(4000))
returns varchar(4000)
as
begin
declare @word nchar(1),@CN varchar(4000)
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 NVARCHAR(100))
RETURNS VARCHAR(100)
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 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
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 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
SELECT DBO.get_English('turing我666QQ中国AK123')