半角、全角字符串互转\提取中文或数字

系统管理员 2022-06-16 11:26 167阅读 0赞

/\*    
半角字符串转全角  
\*/  
if   exists   (select   \*   from   dbo.sysobjects   where   id   =   object\_id(N '\[dbo\].\[convert\_CharSet\_BJ2QJ\] ')    
and   xtype   in   (N 'FN ',   N 'IF ',   N 'TF '))  
drop   function   \[dbo\].\[convert\_CharSet\_BJ2QJ\]  
GO

CREATE   FUNCTION   convert\_CharSet\_BJ2QJ(@AnsiString   nvarchar(2000))         --           输入的字符串，半角    
RETURNS   nvarchar(2000)  
WITH   ENCRYPTION  
AS  
BEGIN  
DECLARE   @i                   int  
,@ret               nvarchar(2000)

select   @i=1  
SET   @ret= ' '  
while   @i <=len(@AnsiString)  
begin  
select   @ret   =   @ret   +   NCHAR(ascii(SUBSTRING(@AnsiString,   @i,   1))+   0xFEE0   )     --   $FEE0      
SET   @i=@i+1  
end

return   @ret  
end

\--  
/\*  
select   dbo.convert\_CharSet\_BJ2QJ(   '12345678.900 ')                                                                                                                                                                                            
\--  
\*/

/\*    
全角字符串转半角  
\*/  
if   exists   (select   \*   from   dbo.sysobjects   where   id   =   object\_id(N '\[dbo\].\[convert\_CharSet\_QJ2BJ\] ')    
and   xtype   in   (N 'FN ',   N 'IF ',   N 'TF '))  
drop   function   \[dbo\].\[convert\_CharSet\_QJ2BJ\]  
GO

CREATE   FUNCTION   convert\_CharSet\_QJ2BJ(@WideString   nvarchar(2000))         --           输入的字符串，全角    
RETURNS   nvarchar(2000)  
WITH   ENCRYPTION  
AS  
BEGIN  
DECLARE   @i                   int  
,@ret               nvarchar(2000)

select   @i=1  
SET   @ret= ' '  
WHILE     SUBSTRING(@WideString,@i,1) <> ' '          
BEGIN          
SET     @ret=@ret+NCHAR(UNICODE(SUBSTRING(@WideString,@i,1))-  
CASE      
WHEN     SUBSTRING(@WideString,@i,1)= '。 '     THEN     12244     ELSE         65248                                        
END     )      
SET     @i=@i+1      
END

return   @ret  
end

\--  
/\*  
select   dbo.convert\_CharSet\_QJ2BJ(   '１２３４５６７８．９。００ ')                                                                                                                                                                                            
\--  
\*/

/\*    
数字字符串转全角。可以包含中文。  
\*/  
if   exists   (select   \*   from   dbo.sysobjects   where   id   =   object\_id(N '\[dbo\].\[convert\_CharSet\_2QJ\] ')    
and   xtype   in   (N 'FN ',   N 'IF ',   N 'TF '))  
drop   function   \[dbo\].\[convert\_CharSet\_2QJ\]  
GO

CREATE   FUNCTION   convert\_CharSet\_2QJ(@AnsiString   nvarchar(2000))         --           输入的字符串，半角    
RETURNS   nvarchar(2000)  
WITH   ENCRYPTION  
AS  
BEGIN  
DECLARE   @i                   int  
,@ret               nvarchar(2000)

select   @i=1  
SET   @ret= ' '  
while   @i <=len(@AnsiString)  
begin  
if   unicode(SUBSTRING(@AnsiString,   @i,   1)) <256  
select   @ret   =   @ret   +   NCHAR(unicode(SUBSTRING(@AnsiString,   @i,   1))+   0xFEE0   )    
else  
select   @ret   =   @ret   +   SUBSTRING(@AnsiString,   @i,   1)            
SET   @i=@i+1  
end

return   @ret  
end

\--  
/\*  
select   dbo.convert\_CharSet\_2QJ(   '７我们12345678.900 ')                                                                                                                                                                                            
\--  
\*/

/\*    
数字字符串转半角。包含汉字的字符串输出   null.  
\*/  
if   exists   (select   \*   from   dbo.sysobjects   where   id   =   object\_id(N '\[dbo\].\[convert\_CharSet\_2BJ\] ')    
and   xtype   in   (N 'FN ',   N 'IF ',   N 'TF '))  
drop   function   \[dbo\].\[convert\_CharSet\_2BJ\]  
GO

CREATE   FUNCTION   convert\_CharSet\_2BJ(@WideString   nvarchar(2000))         --           输入的字符串，全角    
RETURNS   nvarchar(2000)  
WITH   ENCRYPTION  
AS  
BEGIN  
DECLARE   @i                   int  
,@ret               nvarchar(2000)

select   @i=1  
SET   @ret= ' '  
WHILE     SUBSTRING(@WideString,@i,1) <> ' '          
BEGIN    
if   UNICODE(SUBSTRING(@WideString,@i,1))> 255        
SET     @ret=@ret+NCHAR(UNICODE(SUBSTRING(@WideString,@i,1))-  
CASE      
WHEN     SUBSTRING(@WideString,@i,1)= '。 '     THEN     12244     ELSE         65248                                        
END     )    
else  
SET     @ret=@ret+(SUBSTRING(@WideString,@i,1))            
SET     @i=@i+1      
END

return   @ret  
end

\--  
/\*  
select   dbo.convert\_CharSet\_2BJ(   '156１２３４５６７８．９。００ ')      
\--  
\*/

create function c1toc2(@c3 nvarchar(1000))  
returns nvarchar(1000)  
as  
begin  
\--declare @c1 nvarchar(30),@c2 nvarchar(30)  
\--set @c1='～！◎＃￥％……※×（）＿＋；‘：“，。、《》？【】『』'  
\--set @c2='~!@\#$%^&\*()\_+;'':",./<>?\[\]\{\}'  
select @c3=stuff(@c3,number,1,substring(['~!@\#$%^&\*()\_+;'':"",./<>?\[\]\{\}',charindex(substring(@c3,number,1),'][._charindex_substring_c3_number_1]～！◎＃￥％…※×（）＿＋；‘：“”，。、《》？【】『』'),1))  
from master..spt\_values where type='p' and number between 1 and len(@c3) and charindex(substring(@c3,number,1),'～！◎＃￥％…※×（）＿＋；‘：“”，。、《》？【】『』')>0  
set @c3=replace(@c3,'^^','^')  
return @c3  
end  
go  
declare @c nvarchar(1000)  
set @c='你好，今天是周未！（你）吃过没有？他说：“你今天很漂亮……”※《◎【】》、『％＃』这两本书很有意思。'  
go  
\--求将一个字段的多条记录，拼成一条记录

if object\_id('tb') is not null  
   drop table tb  
go  
create table tb  
(  
 编码 varchar(10),  
 名称 varchar(10)  
)  
go  
insert into tb  
select '01','AAA' union all  
select '01','BBB' union all  
select '01','CCC' union all  
select '02','DDD' union all  
select '02','eee' union all  
select '03','fff'  
go  
select 编码,名称=stuff((select '/'+名称 from tb where 编码=a.编码 for xml path('')),1,1,'') from tb a group by 编码  
go  
/\*  
\---

\--提取汉字  
IF OBJECT\_ID('dbo.fn\_china\_word') IS NOT NULL  
DROP FUNCTION dbo.fn\_china\_word  
GO  
CREATE FUNCTION dbo.fn\_china\_word(@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.fn\_china\_word('s上地dfa北京市56中国的首都010sKSD海淀区\*&^')  
GO

\--提取数字  
IF OBJECT\_ID('dbo.fn\_get\_number') IS NOT NULL  
DROP FUNCTION dbo.fn\_get\_number  
GO  
CREATE FUNCTION dbo.fn\_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.fn\_get\_number('sdfa北京市56中国的首都010sKSD海淀区\*&^')  
GO  
\--提取英文  
IF OBJECT\_ID('dbo.fn\_get\_eword') IS NOT NULL  
DROP FUNCTION dbo.fn\_get\_eword  
GO  
CREATE FUNCTION dbo.get\_eword(@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.fn\_get\_eword('sdfa北京市56中国的首都010sKSD海淀区\*&^')  
GO

\--获取拼音首字母函数  
create function fn\_GetPy\_Each\_First(@str nvarchar(4000))  
returns nvarchar(4000)  
as  
begin  
declare @strlen int,@re nvarchar(4000)  
declare @t table(chr nchar(1) collate Chinese\_PRC\_CI\_AS,letter nchar(1))  
insert into @t(chr,letter)  
  select '吖','A' union all select '八','B' union all  
  select '嚓','C' union all select '咑','D' union all  
  select '妸','E' union all select '发','F' union all  
  select '旮','G' union all select '铪','H' union all  
  select '丌','J' union all select '咔','K' union all  
  select '垃','L' union all select '嘸','M' union all  
  select '拏','N' union all select '噢','O' union all  
  select '妑','P' union all select '七','Q' union all  
  select '呥','R' union all select '仨','S' union all  
  select '他','T' union all select '屲','W' union all  
  select '夕','X' union all select '丫','Y' union all  
  select '帀','Z'  
  select @strlen=len(@str),@re=''  
  while @strlen>0  
  begin  
    select top 1 @re=letter+@re,@strlen=@strlen-1  
      from @t a where chr<=substring(@str,@strlen,1)  
      order by chr desc  
    if @@rowcount=0  
      select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1  
  end  
  return(@re)  
end  
go  
select dbo.fn\_GetPy\_Each\_First('大家都是中国人')  
go  
\---获取汉字串中第一个汉字的首字母  
create  function fn\_Getchinese\_firstletter  
(  
  @str nvarchar(1)  
)  
returns nvarchar(1)  
as  
begin  
  declare @word nchar(1),@PY nvarchar(4000)  
  set @PY=''

set @word=left(@str,1)  
    --如果非汉字字符，返回原字符  
    set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901  
                    then (   
                    select top 1 PY   
                    from   
                    (   
                    select 'A' as PY,N'驁' as word  
                    union all select 'B',N'簿'  
                    union all select 'C',N'錯'  
                    union all select 'D',N'鵽'  
                    union all select 'E',N'樲'  
                    union all select 'F',N'鰒'  
                    union all select 'G',N'腂'  
                    union all select 'H',N'夻'  
                    union all select 'J',N'攈'  
                    union all select 'K',N'穒'  
                    union all select 'L',N'鱳'  
                    union all select 'M',N'旀'  
                    union all select 'N',N'桛'  
                    union all select 'O',N'漚'  
                    union all select 'P',N'曝'  
                    union all select 'Q',N'囕'  
                    union all select 'R',N'鶸'  
                    union all select 'S',N'蜶'  
                    union all select 'T',N'籜'  
                    union all select 'W',N'鶩'  
                    union all select 'X',N'鑂'  
                    union all select 'Y',N'韻'  
                    union all select 'Z',N'咗'  
                    ) T   
                    where word>=@word collate Chinese\_PRC\_CS\_AS\_KS\_WS   
                    order by PY ASC  
                          )   
                    else @word   
                end)  
    set @str=right(@str,len(@str)-1)

return @PY  
end

go  
select dbo.fn\_Getchinese\_firstletter('大家都是中国人')  
go

\--过滤重复字符  
IF OBJECT\_ID('dbo.fn\_distinct\_str') IS NOT NULL  
DROP FUNCTION dbo.fn\_distinct\_str  
GO  
CREATE FUNCTION dbo.fn\_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  
select dbo.fn\_distinct\_str('A,A,B,C,C,B,C,k,e,df,',',')  
\--A,B,C  
GO  
\--过滤重复字符2  
IF OBJECT\_ID('dbo.fn\_distinct\_str2') IS NOT NULL  
DROP FUNCTION dbo.fn\_distinct\_str2  
GO  
CREATE FUNCTION dbo.fn\_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.fn\_distinct\_str2('AABCCD')  
\--ABCD  
GO

[._charindex_substring_c3_number_1]: mailto:'%20rel=