[SQL Server] 提取数字、提取英文、提取中文

￡神魔★判官ぃ 2022-06-17 20:55 197阅读 0赞

1.  \--SQL 判断字段值是否有中文  
2.  create  function  fun\_getCN(@str  nvarchar(4000))      
3.  returns  nvarchar(4000)      
4.    as      
5.    begin      
6.    declare  @word  nchar(1),@CN  nvarchar(4000)      
7.    set  @CN=''      
8.    while  len(@str)>0      
9.    begin      
10.   set  @word=left(@str,1)      
11.   if unicode(@word)  between  19968  and  19968+20901    
12.       set  @CN=@CN+@word  
13.   set  @str=right(@str,len(@str)-1)      
14.   end      
15.   return  @CN      
16.   end      
17. select dbo.fun\_getCN('ASDKG论坛KDL')  
18. \--论坛  
19. select dbo.fun\_getCN('ASDKG論壇KDL')  
20. \--論壇  
21. select dbo.fun\_getCN('ASDKDL')  
22. \--空

\--SQL 判断字段值是否有中文 create function fun\_getCN(@str nvarchar(4000)) returns nvarchar(4000) as begin declare @word nchar(1),@CN nvarchar(4000) set @CN='' while len(@str)>0 begin set @word=left(@str,1) if unicode(@word) between 19968 and 19968+20901 set @CN=@CN+@word set @str=right(@str,len(@str)-1) end return @CN end select dbo.fun\_getCN('ASDKG论坛KDL') --论坛 select dbo.fun\_getCN('ASDKG論壇KDL') --論壇 select dbo.fun\_getCN('ASDKDL') --空

\-------------------------------------

\--提取数字

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  
\--测试  
PRINT DBO.GET\_NUMBER('呵呵ABC123ABC')  
GO  
\--123

\--------------------------------------------------------------------  
\--提取英文  
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  
\--测试  
PRINT DBO.GET\_STR('呵呵ABC123ABC')  
GO

\--------------------------------------------------------------------  
\--提取中文  
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('%\[^吖-座\]%',@S) > 0  
SET @S = STUFF(@S,PATINDEX('%\[^吖-座\]%',@S),1,N'')  
RETURN @S  
END  
GO  
PRINT DBO.CHINA\_STR('呵呵ABC123ABC')  
GO

\--------------------------------------------------------------------  
\--过滤重复字符  
IF OBJECT\_ID('DBO.DISTINCT\_STR') IS NOT NULL  
DROP FUNCTION DBO.DISTINCT\_STR  
GO  
CREATE FUNCTION DBO.DISTINCT\_STR(@S NVARCHAR(100),@SPLIT VARCHAR(50))  
RETURNS VARCHAR(100)  
AS  
BEGIN  
IF @S IS NULL RETURN(NULL)  
DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)  
IF LEFT(@S,1)<>@SPLIT  
SET @S = @SPLIT+@S  
IF RIGHT(@S,1)<>@SPLIT  
SET @S = @S+@SPLIT  
WHILE CHARINDEX(@SPLIT,@S)>0 AND LEN(@S)<>1  
BEGIN  
SET @INDEX = CHARINDEX(@SPLIT,@S)  
SET @TEMP = LEFT(@S,CHARINDEX(@SPLIT,@S,@INDEX+LEN(@SPLIT)))  
IF @NEW IS NULL   
SET @NEW = ISNULL(@NEW,'')+@TEMP  
ELSE  
SET @NEW = ISNULL(@NEW,'')+REPLACE(@TEMP,@SPLIT,'')+@SPLIT  
WHILE CHARINDEX(@TEMP,@S)>0  
BEGIN  
SET @S=STUFF(@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT),CHARINDEX(@SPLIT,@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT))-CHARINDEX(@TEMP,@S),'')  
END  
END  
RETURN RIGHT(LEFT(@NEW,LEN(@NEW)-1),LEN(LEFT(@NEW,LEN(@NEW)-1))-1)  
END  
GO  
PRINT DBO.DISTINCT\_STR('A,A,B,C,C,B,C,',',')  
\--A,B,C  
GO

\--------------------------------------------------------------------  
\--过滤重复字符2  
IF OBJECT\_ID('DBO.DISTINCT\_STR2') IS NOT NULL  
DROP FUNCTION DBO.DISTINCT\_STR2  
GO  
CREATE FUNCTION DBO.DISTINCT\_STR2(@S varchar(8000))  
RETURNS VARCHAR(100)  
AS  
BEGIN  
IF @S IS NULL RETURN(NULL)  
DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)  
WHILE LEN(@S)>0  
BEGIN  
SET @NEW=ISNULL(@NEW,'')+LEFT(@S,1)  
SET @S=REPLACE(@S,LEFT(@S,1),'')  
END  
RETURN @NEW  
END  
GO  
SELECT DBO.DISTINCT\_STR2('AABCCD')  
\--ABCD  
GO  
  
\--------------------------------------------------------------------  
IF OBJECT\_ID('DBO.SPLIT\_STR') IS NOT NULL  
DROP FUNCTION DBO.SPLIT\_STR  
GO  
CREATE FUNCTION DBO.SPLIT\_STR(  
@S varchar(8000),      --包含多个数据项的字符串  
@INDEX int,             --要获取的数据项的位置  
@SPLIT varchar(10)     --数据分隔符  
)  
RETURNS VARCHAR(100)  
AS  
BEGIN  
IF @S IS NULL RETURN(NULL)  
DECLARE @SPLITLEN int  
SELECT @SPLITLEN=LEN(@SPLIT+'A')-2  
WHILE @INDEX>1 AND CHARINDEX(@SPLIT,@S+@SPLIT)>0  
SELECT @INDEX=@INDEX-1,@S=STUFF(@S,1,CHARINDEX(@SPLIT,@S+@SPLIT)+@SPLITLEN,'')  
RETURN(ISNULL(LEFT(@S,CHARINDEX(@SPLIT,@S+@SPLIT)-1),''))  
END  
GO  
PRINT DBO.SPLIT\_STR('AA|BB|CC',2,'|')  
\--  
GO