SQL Server版FAQ帖欢迎大家踊跃补充

CSDN 2012-05-04 02:39:56

加精

...全文

20187 177 打赏收藏转发到动态举报

写回复

用AI写文章

177 条回复

切换为时间正序

请发表友善的回复…

发表回复

sysplay 2015-01-19

打赏
举报

先收藏，再慢慢理解。

BiChangQing 2014-11-03

打赏
举报

收藏，慢慢学

janxuan 2014-09-28

打赏
举报

总结的真好，

jxfujuan1985 2014-07-17

打赏
举报

好帖，谢谢楼主分享。

myue 2014-03-27

打赏
举报

好资料，mark一下

a785510512 2014-01-09

打赏
举报

果断留个爪，，然后再看

ChinaITOldMan 2013-12-04

打赏
举报

good post,collect!

flovem_1 2013-11-25

打赏
举报

菜鸟来支持一下

reste51 2013-09-10

打赏
举报

多谢楼主教导，指导了很多

jack_110_jack 2013-09-04

打赏
举报

非常好的，占位中，学习中。。。

---涛声依旧--- 2013-08-28

打赏
举报

--创建取汉字首字母函数(第三版) create function [dbo].[ufn_GetPinYin_V3] ( @col varchar(1000) ) returns varchar(1000) as begin declare @cyc int,@len int,@sql varchar(1000),@char varbinary(20) select @cyc = 1,@len = len(@col),@sql = '' while @cyc <= @len begin select @char = cast(substring(@col, @cyc, 1) as varbinary) declare @maco table (bcode varbinary(20),ecode varbinary(20),letter varchar(10)) insert into @maco select 0XB0A1,0XB0C4,'A' union all select 0XB0C5,0XB2C0,'B' union all select 0XB2C1,0XB4ED,'C' union all select 0XB4EE,0XB6E9,'D' union all select 0XB6EA,0XB7A1,'E' union all select 0XB7A2,0XB8C0,'F' union all select 0XB8C1,0XB9FD,'G' union all select 0XB9FE,0XBBF6,'H' union all select 0XBBF7,0XBFA5,'J' union all select 0XBFA6,0XC0AB,'K' union all select 0XC0AC,0XC2E7,'L' union all select 0XC2E8,0XC4C2,'M' union all select 0XC4C3,0XC5B5,'N' union all select 0XC5B6,0XC5BD,'O' union all select 0XC5BE,0XC6D9,'P' union all select 0XC6DA,0XC8BA,'Q' union all select 0XC8BB,0XC8F5,'R' union all select 0XC8F6,0XCBF9,'S' union all select 0XCBFA,0XCDD9,'T' union all select 0XCDDA,0XCEF3,'W' union all select 0XCEF4,0XD1B8,'X' union all select 0XD1B9,0XD4D0,'Y' union all select 0XD4D1,0XD7F9,'Z' select top 1 @sql=@sql+letter from @maco where @char between bcode and ecode set @cyc = @cyc + 1 end return @sql end GO --创建取汉字首字母函数(第四版) create function [dbo].[ufn_GetPinYin_V4] ( @col varchar(1000) ) returns varchar(1000) begin declare @cyc int,@len int,@sql varchar(1000),@char varbinary(20) select @cyc = 1,@len = len(@col),@sql = '' while @cyc <= @len begin select @char = cast(substring(@col, @cyc, 1) as varbinary) if @char>=0XB0A1 and @char<=0XB0C4 set @sql=@sql+'A' else if @char>=0XB0C5 and @char<=0XB2C0 set @sql=@sql+'B' else if @char>=0XB2C1 and @char<=0XB4ED set @sql=@sql+'C' else if @char>=0XB4EE and @char<=0XB6E9 set @sql=@sql+'D' else if @char>=0XB6EA and @char<=0XB7A1 set @sql=@sql+'E' else if @char>=0XB7A2 and @char<=0XB8C0 set @sql=@sql+'F' else if @char>=0XB8C1 and @char<=0XB9FD set @sql=@sql+'G' else if @char>=0XB9FE and @char<=0XBBF6 set @sql=@sql+'H' else if @char>=0XBBF7 and @char<=0XBFA5 set @sql=@sql+'J' else if @char>=0XBFA6 and @char<=0XC0AB set @sql=@sql+'K' else if @char>=0XC0AC and @char<=0XC2E7 set @sql=@sql+'L' else if @char>=0XC2E8 and @char<=0XC4C2 set @sql=@sql+'M' else if @char>=0XC4C3 and @char<=0XC5B5 set @sql=@sql+'N' else if @char>=0XC5B6 and @char<=0XC5BD set @sql=@sql+'O' else if @char>=0XC5BE and @char<=0XC6D9 set @sql=@sql+'P' else if @char>=0XC6DA and @char<=0XC8BA set @sql=@sql+'Q' else if @char>=0XC8BB and @char<=0XC8F5 set @sql=@sql+'R' else if @char>=0XC8F6 and @char<=0XCBF9 set @sql=@sql+'S' else if @char>=0XCBFA and @char<=0XCDD9 set @sql=@sql+'T' else if @char>=0XCDDA and @char<=0XCEF3 set @sql=@sql+'W' else if @char>=0XCEF4 and @char<=0XD1B8 set @sql=@sql+'X' else if @char>=0XD1B9 and @char<=0XD4D0 set @sql=@sql+'Y' else if @char>=0XD4D1 and @char<=0XD7F9 set @sql=@sql+'Z' set @cyc = @cyc + 1 end return @sql end GO

---涛声依旧--- 2013-08-28

打赏
举报

--创建取汉字首字母函数(第二版) create function [dbo].[ufn_GetPinYin_V2](@Str varchar(500)='') returns varchar(500) as begin declare @strlen int,@return varchar(500),@ii int declare @n int,@c char(1),@chn nchar(1) select @strlen=len(@str),@return='',@ii=0 set @ii=0 while @ii<@strlen begin select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1) if @chn>'z' select @n = @n +1 ,@c = case chn when @chn then char(@n) else @c end from( select top 27 * from ( select chn = '吖' union all select '八' union all select '嚓' union all select '咑' union all select '妸' union all select '发' union all select '旮' union all select '铪' union all select '丌' --because have no 'i' union all select '丌' union all select '咔' union all select '垃' union all select '嘸' union all select '拏' union all select '噢' union all select '妑' union all select '七' union all select '呥' union all select '仨' union all select '他' union all select '屲' --no 'u' union all select '屲' --no 'v' union all select '屲' union all select '夕' union all select '丫' union all select '帀' union all select @chn) as a order by chn COLLATE Chinese_PRC_CI_AS ) as b else set @c='a' set @return=@return+@c end return(@return) end GO

---涛声依旧--- 2013-08-28

打赏
举报

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --创建取汉字首字母函数(第一版) create function [dbo].[ufn_GetPinYin_V1] (@str nvarchar(4000)) returns nvarchar(4000) as begin declare @word nchar(1),@py nvarchar(4000) set @py='' while len(@str)>0 begin 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) end return @PY end GO