获取中文拼音首字母的，用到了编码顺序来简化，还有优化空间。
CREATE FUNCTION func_chinese_spell(str VARCHAR(2000)) RETURNS VARCHAR(2000) AS
$$
DECLARE
word NCHAR(1);
code VARCHAR(2000);
i INTEGER;
chnstr VARCHAR(2000);
BEGIN
code := '';
i := 1;
chnstr := str;
WHILE LENGTH(chnstr)>0 LOOP
word := SUBSTRING(str,i,1);
code := code || CASE WHEN (ASCII(word) BETWEEN 19968 AND 19968+20901) THEN
(
SELECT p FROM
(
SELECT 'A' as p,N'驁' as w
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 w>=word ORDER BY p ASC LIMIT 1
)
ELSE word END;
i := i + 1;
chnstr := SUBSTRING(str,i,LENGTH(str)-i + 1);
END LOOP;
RETURN code;
END;
$$
LANGUAGE plpgsql;2014,swish，原版首发：http://blog.qdac.cc/?p=1281，自由使用，保留版权
CREATE OR REPLACE FUNCTION CnFirstChar(s character varying)
RETURNS character varying AS
$BODY$
declare
retval character varying;
c character varying;
l integer;
b bytea;
w integer;
begin
l=length(s);
retval='';
while l>0 loop
c=left(s,1);
b=convert_to(c,'GB18030')::bytea;
if get_byte(b,0)<127 then
retval=retval || upper(c);
elsif length(b)=2 then
begin
w=get_byte(b,0)*256+get_byte(b,1);
--汉字GBK编码按拼音排序，按字符数来查找，基于概率来说，效率应该比挨个强:)
if w between 48119 and 49061 then --"J";48119;49061;942
retval=retval || 'J';
elsif w between 54481 and 55289 then --"Z";54481;55289;808
retval=retval || 'Z';
elsif w between 53689 and 54480 then --"Y";53689;54480;791
retval=retval || 'Y';
elsif w between 51446 and 52208 then --"S";51446;52208;762
retval=retval || 'S';
elsif w between 52980 and 53640 then --"X";52980;53640;660
retval=retval || 'X';
elsif w between 49324 and 49895 then --"L";49324;49895;571
retval=retval || 'L';
elsif w between 45761 and 46317 then --"C";45761;46317;556
retval=retval || 'C';
elsif w between 45253 and 45760 then --"B";45253;45760;507
retval=retval || 'B';
elsif w between 46318 and 46825 then --"D";46318;46825;507
retval=retval || 'D';
elsif w between 47614 and 48118 then --"H";47614;48118;504
retval=retval || 'H';
elsif w between 50906 and 51386 then --"Q";50906;51386;480
retval=retval || 'Q';
elsif w between 52218 and 52697 then --"T";52218;52697;479
retval=retval || 'T';
elsif w between 49896 and 50370 then --"M";49896;50370;474
retval=retval || 'M';
elsif w between 47297 and 47613 then --"G";47297;47613;316
retval=retval || 'G';
elsif w between 47010 and 47296 then--"F";47010;47296;286
retval=retval || 'F';
elsif w between 50622 and 50905 then--"P";50622;50905;283
retval=retval || 'P';
elsif w between 52698 and 52979 then--"W";52698;52979;281
retval=retval || 'W';
elsif w between 49062 and 49323 then--"K";49062;49323;261
retval=retval || 'K';
elsif w between 50371 and 50613 then --"N";50371;50613;242
retval=retval || 'N';
elsif w between 46826 and 47009 then--"E";46826;47009;183
retval=retval || 'E';
elsif w between 51387 and 51445 then--"R";51387;51445;58
retval=retval || 'R';
elsif w between 45217 and 45252 then --"A";45217;45252;35
retval=retval || 'A';
elsif w between 50614 and 50621 then --"O";50614;50621;7
retval=retval || 'O';
end if;
end;
end if;
s=substring(s,2,l-1);
l=l-1;
end loop;
return retval;
end;
$BODY$
LANGUAGE plpgsql IMMUTABLE;代码如下:
--函数
CREATE function fn_GetPy(@str nvarchar(4000))
returns nvarchar(4000)
--WITH ENCRYPTION
as
begin
declare @intLenint
declare @strRetnvarchar(4000)
declare @temp nvarchar(100)
set @intLen = len(@str)
set @strRet = ''
while @intLen > 0
begin
set @temp = ''
select @temp = case
when substring(@str,@intLen,1) >= '帀' then 'Z'
when substring(@str,@intLen,1) >= '丫' then 'Y'
when substring(@str,@intLen,1) >= '夕' then 'X'
when substring(@str,@intLen,1) >= '屲' then 'W'
when substring(@str,@intLen,1) >= '他' then 'T'
when substring(@str,@intLen,1) >= '仨' then 'S'
when substring(@str,@intLen,1) >= '呥' then 'R'
when substring(@str,@intLen,1) >= '七' then 'Q'
when substring(@str,@intLen,1) >= '妑' then 'P'
when substring(@str,@intLen,1) >= '噢' then 'O'
when substring(@str,@intLen,1) >= '拏' then 'N'
when substring(@str,@intLen,1) >= '嘸' then 'M'
when substring(@str,@intLen,1) >= '垃' then 'L'
when substring(@str,@intLen,1) >= '咔' then 'K'
when substring(@str,@intLen,1) >= '丌' then 'J'
when substring(@str,@intLen,1) >= '铪' then 'H'
when substring(@str,@intLen,1) >= '旮' then 'G'
when substring(@str,@intLen,1) >= '发' then 'F'
when substring(@str,@intLen,1) >= '妸' then 'E'
when substring(@str,@intLen,1) >= '咑' then 'D'
when substring(@str,@intLen,1) >= '嚓' then 'C'
when substring(@str,@intLen,1) >= '八' then 'B'
when substring(@str,@intLen,1) >= '吖' then 'A'
else rtrim(ltrim(substring(@str,@intLen,1)))
end
--对于汉字特殊字符，不生成拼音码
if (ascii(@temp)>127) set @temp = ''
--对于英文中小括号，不生成拼音码
if @temp = '(' or @temp = ')' set @temp = ''
select @strRet = @temp + @strRet
set @intLen = @intLen - 1
end
return lower(@strRet)
end
go
--调用
select dbo.fn_getpy('张三')
--返回：zs
答！： 2:
取汉字拼音首字母的存储过程
Create function fun_getPY ( @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 