当前页面位置: » 丰搜网 » 文档中心 » 详细内容
用于模糊查询的原创procedure
贴个自己的小代码，采用偷懒的方法做的。
先用c++把字库弄出来，导成表存起来。
然后利用sql server 自己的排序方式（codepage:936，发布时要求客户安装时只能用codepage:936）对总的字库进行排序。找出每个拼音的头。
缺点：
1. sql server 自己的排序对中文有问题，在7和2000上都存在。
2. 多音字没有处理，只能手工再处理。
见笑，见笑。
create procedure sp_getgbinitials
@srcname name = null,
@dstname name = null output
as
select @srcname = rtrim(ltrim(@srcname))
if len(@srcname) <= 0
begin
select @dstname = null
return
end
select @dstname = ''
declare @letter varchar(10), @len int
select @letter = '', @len = 0
while(@len <= len(@srcname))
begin
-- to ensure that table sysobjects has records.
-- to get soundsequence (based on simplify chinese 936 coding)
select @letter = case
when substring(@srcname, @len, 1) between '阿' and '鏊' or substring(upper(@srcname), @len, 1) = 'a' then 'a'
when substring(@srcname, @len, 1) between '八' and '簿' or substring(@srcname, @len, 1) = '8' or substring(upper(@srcname), @len, 1) = 'b' then 'b'
when substring(@srcname, @len, 1) between '嚓' and '错' or substring(upper(@srcname), @len, 1) = 'c' then 'c'
when substring(@srcname, @len, 1) between '哒' and '跺' or substring(upper(@srcname), @len, 1) = 'd' then 'd'
when substring(@srcname, @len, 1) between '屙' and '贰' or substring(@srcname, @len, 1) = '2' or substring(upper(@srcname), @len, 1) = 'e' then 'e'
when substring(@srcname, @len, 1) between '发' and '馥' or substring(upper(@srcname), @len, 1) = 'f' then 'f'
when substring(@srcname, @len, 1) between '旮' and '过' or substring(upper(@srcname), @len, 1) = 'g' then 'g'
when substring(@srcname, @len, 1) between '铪' and '蠖' or substring(upper(@srcname), @len, 1) = 'h' then 'h'
when (substring(@srcname, @len, 1) between '丌' and '竣' or substring(@srcname, @len, 1) = '9' or substring(upper(@srcname), @len, 1) = 'j') and substring(@srcname, 1, 1) <> '她' then 'j'
when substring(@srcname, @len, 1) between '咔' and '廓' or substring(upper(@srcname), @len, 1) = 'k' then 'k'
when substring(@srcname, @len, 1) between '垃' and '雒' or substring(@srcname, @len, 1) in ('0', '6') or substring(upper(@srcname), @len, 1) = 'l' then 'l'
when substring(@srcname, @len, 1) between '妈' and '穆' or substring(upper(@srcname), @len, 1) = 'm' then 'm'
when substring(@srcname, @len, 1) between '拿' and '糯' or substring(upper(@srcname), @len, 1) = 'n' then 'n'
when substring(@srcname, @len, 1) between '噢' and '沤' or substring(upper(@srcname), @len, 1) = 'o' then 'o'
when substring(@srcname, @len, 1) between '趴' and '曝' or substring(upper(@srcname), @len, 1) = 'p' then 'p'
when substring(@srcname, @len, 1) between '七' and '群' or substring(@srcname, @len, 1) = '7' or substring(upper(@srcname), @len, 1) = 'q' then 'q'
when substring(@srcname, @len, 1) between '蚺' and '箬' or substring(upper(@srcname), @len, 1) = 'r' then 'r'
when substring(@srcname, @len, 1) between '仨' and '锁' or substring(@srcname, @len, 1) in ('3', '4') or substring(upper(@srcname), @len, 1) = 's' then 's'
when substring(@srcname, @len, 1) between '他' and '箨' or substring(upper(@srcname), @len, 1) = 't' or substring(@srcname, 1, 1) = '她' then 't'
when substring(@srcname, @len, 1) between '哇' and '鋈' or substring(@srcname, @len, 1) = '5' or substring(upper(@srcname), @len, 1) = 'w' then 'w'
when substring(@srcname, @len, 1) between '夕' and '蕈' or substring(upper(@srcname), @len, 1) = 'x' then 'x'
when substring(@srcname, @len, 1) between '丫' and '蕴' or substring(@srcname, @len, 1) = '1' or substring(upper(@srcname), @len, 1) = 'y' then 'y'
when substring(@srcname, @len, 1) between '匝' and '做' or substring(upper(@srcname), @len, 1) = 'z' then 'z'
when substring(upper(@srcname), @len, 1) = 'i' then 'i'
when substring(upper(@srcname), @len, 1) = 'u' then 'u'
when substring(upper(@srcname), @len, 1) = 'v' then 'v'
else ''
end
from configures
select @len = @len + 1
select @dstname = @dstname + @letter
end
if len(@dstname) = 0 select @dstname = null
go
declare @out nvarchar(100)
execute sp_getgbinitials '测试用例见笑见笑', @out output
select @out
