根据汉字的首字母生产拼音码进行查询

 

公司里原先的代码，实在没看懂。。。。

主要是在Sql中实现

USE [Xpos_refer_database] 
GO 
/****** Object:  UserDefinedFunction [dbo].[GetLetter]    Script Date: 04/07/2012 09:22:25 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
ALTER function [dbo].[GetLetter](@str nvarchar(4000),@type int) 
returns nvarchar(4000) 
-- WITH   ENCRYPTION 
as 
begin 
declare @intLen int --長度
declare @strRet nvarchar(4000)--返回值
declare @temp nvarchar(100) --臨時變量
set @intLen = len(@str) 
set @strRet = '' 

declare @i int --臨時變量
select @i=1 
while (@intLen > 0 and @type>0) 
begin 
    set @temp = '' 
    select @temp = case 
        when substring(@str,@i,1) > = '帀 ' then 'Z' 
        when substring(@str,@i,1) > = '丫 ' then 'Y' 
        when substring(@str,@i,1) > = '夕 ' then 'X' 
        when substring(@str,@i,1) > = '屲 ' then 'W' 
        when substring(@str,@i,1) > = '他 ' then 'T' 
        when substring(@str,@i,1) > = '仨 ' then 'S' 
        when substring(@str,@i,1) > = '呥 ' then 'R' 
        when substring(@str,@i,1) > = '七 ' then 'Q' 
        when substring(@str,@i,1) > = '妑 ' then 'P' 
        when substring(@str,@i,1) > = '噢 ' then 'O' 
        when substring(@str,@i,1) > = '拏 ' then 'N' 
        when substring(@str,@i,1) > = '嘸 ' then 'M' 
        when substring(@str,@i,1) > = '垃 ' then 'L' 
        when substring(@str,@i,1) > = '咔 ' then 'K' 
        when substring(@str,@i,1) > = '丌 ' then 'J' 
        when substring(@str,@i,1) > = '铪 ' then 'H' 
        when substring(@str,@i,1) > = '旮 ' then 'G' 
        when substring(@str,@i,1) > = '发 ' then 'F' 
        when substring(@str,@i,1) > = '妸 ' then 'E' 
        when substring(@str,@i,1) > = '咑 ' then 'D' 
        when substring(@str,@i,1) > = '嚓 ' then 'C' 
        when substring(@str,@i,1) > = '八 ' then 'B' 
        when substring(@str,@i,1) > = '吖 ' then 'A' 
        else rtrim(ltrim(substring(@str,@i,1))) 
    end 

    --对于汉字特殊字符，不生成拼音码 
 if (ascii(@temp)> 127) set @temp = ' ' 
    --对于英文中小括号，不生成拼音码 
 if @temp = '(' or @temp = ')' set @temp = ' ' 
    select @strRet = @strRet +@temp 
    set @intLen = @intLen - 1 
    if @type<=10 set @type=@type-1 
    set @i=@i+1 
end--while
 
    return lower(@strRet) 
end

这个函数是在Sql中“标量值函数”下面的

查询的Sql语句：

select distinct D_CITY as 城市,upper(dbo.GetLetter(D_CITY,1)) as 拼音码 from ref_addr3 order by upper(dbo.GetLetter(D_CITY,1))

查询结果：