汉字拼音索引

【转大富翁笔记】

1.首先对部门表加一个拼音码字段,然后建立函数

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  

2.然后对部门表加个触发器
CREATE TRIGGER [NamePY] ON [dbo].[Department]
FOR INSERT, UPDATE
AS
declare @name varchar(100),@npy varchar(100),@cid char(10)

if(update(name))
begin
  select @name=name,@cid=id from inserted
  set @npy=dbo.fun_getPY(@name)

  update Department set namepy=@npy where id=@cid
end

3.数据库部分完成, 接下来是用combobox实现自动下拉
用keydown事件, KEYPRESS肯定是不行的, 还要打开autodropdown
procedure TFrm_Employee.RzComboBox3KeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
var
  flag : boolean ;
  KeyChar : Char ;
begin
  flag := false ;
  KeyChar := chr(key);
  if ((key = VK_BACK) and (length(keyValue) > 0 )) then
  begin
    keyValue := copy(keyValue , 1 , length(keyValue) - 1 ) ;   //keyValue 全局变量
    flag := true ;
  end
  else if (KeyChar in ['A'..'Z', 'a'..'z']) then
  begin
    keyValue := keyValue + KeyChar ;
    flag := true ;
  end
  else if (KeyChar = #13) then
    flag := true ;

  if flag then
  begin
    deptQry.Filtered := false ;
    if flag and ((length(keyValue) > 0 )) then
    begin
      deptQry.Filter := 'namepy like ' + quotedstr(keyValue + '%') ;
      deptQry.Filtered := true;
    end;
    deptQry.First ;
    RzCombobox3.Clear;
    while not deptQry.Eof do
    begin
      RzCombobox3.Items.Add('['+ deptQry.FieldByName('ID').AsString+']'+deptQry.FieldByName('Name').AsString);   //部门筛选
      deptQry.Next;
    end;
    RzCombobox3.Items.Insert(0,'全部部门');
  end;

  //RzCombobox3.Text:=keyValue;      //防止倒序插入
  RzCombobox3.SelStart:=length(keyValue);  
end;

存在问题:
1.字符从头开始(倒序)插入, 如果用selStart则以前的字符串消失
  如果用RzCombobox.text:=keyValue, 则还是会在之后再在头里插入

2.用回车不能选择