usp_yy_getpyzt.sql

可以通过该存储过程获取选定字符的拼音或五笔字头

if exists(select * from sysobjects where name='usp_yy_getpyzt')
	drop procedure usp_yy_getpyzt
go
create procedure usp_yy_getpyzt	
	@inputstr varchar(255),
	@pybz	smallint,				
	@outputstr varchar(255) output
as
/**********
[版本号]4.0.0.0.0
[创建时间]2001.10.19
[作者]王奕
[版权] Copyright ? 1998-2001上海金仕达-卫宁医疗信息技术有限公司
[描述]生成单个拼音或五笔
[功能说明]
	拼音和五笔字头
[参数说明]
	@inputstr varchar(255),   --输入汉字
	@pybz	smallint,	  --0：生成拼音 1：生成五笔
	@outputstr varchar(255) output --输出的拼音和五笔
[返回值]
[结果集、排序]
[调用的usp] 无
[调用实例]
**********/
set nocount on

begin
declare @chzchar varchar(2),	
   	@chz	varchar(2),
   	@cpy	varchar(255),
   	@i	integer,
	@bsm	varchar(2)

	select  @inputstr=ltrim(rtrim(@inputstr))
  	select	@cpy='',
  		@chz='',
		@chzchar='',
		@i=1
	while @i<=datalength(@inputstr)
	begin
      	select @chzchar=substring(@inputstr,@i,1)
		if ascii(@chzchar)>127
      	begin
			if @chz=''
         		select @chz=@chzchar
			else
				select @chz=@chz+@chzchar
      	end
      	else if @chzchar is Null
		begin
			break
		end	
		else if ascii(@chzchar) between 48 and 57
		begin
         	select @cpy=@cpy+(case ascii(@chzchar) when 48 then 'l' when 49 then 'y' when 50 then 'e'
				when 51 then 's' when 52 then 's' when 53 then 'w' when 54 then 'l' when 55 then 'q'
				when 56 then 'b' when 57 then 'j' end)
         	select @chz=''
      	end
		else if (ascii(@chzchar) between 65 and 90) or (ascii(@chzchar) between 97 and 122)
		begin
			select @cpy=@cpy+rtrim(ltrim(lower(@chzchar)))
         	select @chz=''
		end

      	if datalength(@chz)=2
      	begin
			if exists(select * from hzpytmp where bhz=@chz)
			begin
				if @pybz=0
					select @bsm=bsm from hzpytmp where bhz=@chz
				else
					select @bsm=wbm from hzpytmp where bhz=@chz
				select @cpy=@cpy+ltrim(rtrim(@bsm))
			end
-- 	        else
-- 	         	 select @cpy=@cpy+'*'

	        select @chz=''
      	end
		select @i=@i+1
  	end

	select @outputstr=ltrim(rtrim(@cpy))
	return
end
go