sqlserver提取中文_提取英文_提取数字（函数）

--【提取中文字符】
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('123我KK哈哈45')  
--中国
 
 
select unicode('一')
select unicode('龥')
 
unicode中文编码范围：19968~40869

----------------------------------------------------------------
----------------------------------------------------------------
--【提取中文】
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('123我KK哈哈45')
 
----------------------------------------------------------------
----------------------------------------------------------------
--【提取数字】
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
 
select dbo.GET_NUMBER2('123我KK哈哈45')
 
----------------------------------------------------------------
----------------------------------------------------------------
--【提取英文】
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('123我KK哈哈45')
 
----------------------------------------------------------------
----------------------------------------------------------------
--【提取特殊字符】
DROP FUNCTION DBO.FN_GetSpecialCharacter
GO
ALTER FUNCTION DBO.FN_GetSpecialCharacter(@S VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
  WHILE PATINDEX('%[a-z0-9吖-座]%',@S) > 0
  BEGIN
    SET @s=stuff(@s,patindex('%[a-z0-9吖-座]%',@s),1,'')
  END
  RETURN @S
END
GO

SELECT DBO.FN_GetSpecialCharacter('123我KK$#&哈哈4^&5')