Mindon.IDEA

Air off, Mind on ~ / Javascript+Golang, Sci, Health… /

数据库汉字搜索

BlogMS original blog key: 1001125911, blog id: airoff History stat: 浏览/评论:535/1 , 日期:2006年3月29日 16:06

MySQL5 之汉字首拼音字母查询存储过程

—————————-

DROP TABLE IF EXISTS hz2py;

CREATE TABLE hz2py (

PY char(1) character set utf8 NOT NULL,

HZ char(1) NOT NULL default ‘’,

PRIMARY KEY (PY)

) ENGINE=InnoDB DEFAULT CHARSET=gbk;

INSERT INTO hz2py (PY,HZ) VALUES

(‘A’,‘驁’),

(‘B’,‘簿’),

(‘C’,‘錯’),

(’D’,‘鵽’),

(‘E’,‘樲’),

(‘F’,‘鰒’),

(‘G’,‘腂’),

(‘H’,‘夻’),

(‘J’,‘攈’),

(‘K’,‘穒’),

(‘L’,‘鱳’),

(’M’,‘旀’),

(‘N’,‘桛’),

(‘O’,‘漚’),

(‘P’,‘曝’),

(‘Q’,‘囕’),

(‘R’,‘鶸’),

(’S’,‘蜶’),

(’T’,‘籜’),

(‘W’,‘鶩’),

(‘X’,‘鑂’),

(‘Y’,‘韻’),

(‘Z’,‘咗’);

CREATE FUNCTION more.firstPY(words varchar(255)) RETURNS mediumtext

BEGIN

declare fpy char(1);

declare pc char(1);

declare cc char(4);

set @fpy = UPPER(left(words,1));

set @pc = (CONVERT(@fpy USING gbk));

set @cc = hex(@pc);

if @cc >= "8140" and @cc <="FEA0" then

begin

select PY into @fpy from HZ2PY where hz>=@pc limit 1;

end;

elseif Instr("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",@fpy) = 0 then

set @fpy = "";

end if;

Return @fpy;

END

MS-SQL-SERVER 之汉字笔画,首拼音字母查询存储过程

—————————-

if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[chineseBH]‘) and xtype in (N’FN’, N’IF’, N’TF’))

drop function [dbo].[chineseBH]

GO

if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[firstAllPY]‘) and xtype in (N’FN’, N’IF’, N’TF’))

drop function [dbo].[firstAllPY]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

create function chineseBH(@str nvarchar(4000))

returns int

as

begin

declare @word nchar(1),@n int

set @n=0

while len(@str)>0

begin

set @word=left(@str,1)

–如果非汉字,笔划当0计

set @n=@n+(case when unicode(@word) between 19968 and 19968+20901

then (select top 1 id from (

select 1 as id,N’亅’ as word

union all select 2,N’阝’

union all select 3,N’马’

union all select 4,N’风’

union all select 5,N’龙’

union all select 6,N’齐’

union all select 7,N’龟’

union all select 8,N’齿’

union all select 9,N’鸩’

union all select 10,N’龀’

union all select 11,N’龛’

union all select 12,N’龂’

union all select 13,N’龆’

union all select 14,N’龈’

union all select 15,N’龊’

union all select 16,N’龍’

union all select 17,N’龠’

union all select 18,N’龎’

union all select 19,N’龐’

union all select 20,N’龑’

union all select 21,N’龡’

union all select 22,N’龢’

union all select 23,N’龝’

union all select 24,N’齹’

union all select 25,N’龣’

union all select 26,N’龥’

union all select 27,N’齈’

union all select 28,N’龞’

union all select 29,N’麷’

union all select 30,N’鸞’

union all select 31,N’麣’

union all select 32,N’龖’

union all select 33,N’龗’

union all select 35,N’齾’

union all select 36,N’齉’

union all select 39,N’靐’

union all select 64,N’龘’

) T

where word>=@word collate Chinese_PRC_Stroke_CS_AS_KS_WS

order by id ASC) else 0 end)

set @str=right(@str,len(@str)-1)

end

return @n

end

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

create function firstAllPY(@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

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Comments