MS SQL Server 2000 Chinese fuzzy Search stored procedures and functions

server| stored Procedures | functions | chinese

These stored procedures/functions, which can be used in MS SQL Server 2000, contain two sets of stored procedures that function the same, except that the applicable Chinese characters have different internal code ranges.
These stored procedures and functions support GBK (Continental Simplified Chinese and Traditional Chinese) and BIG5 (Traditional Chinese in Taiwan and Hong Kong)
These functions willused directly in the SQL statement., very convenient to use.
can be found in accordance with the text containing similar pronunciation, you can deal with pronunciation, you can find a similar pronunciation of the word records, in search of names especially useful.
These stored procedures are all written in C + + Builder, and the pronunciation of Chinese characters all by C + + processing, higher efficiency, than the Chinese pronunciation stored in the database with SQL query faster

"Licensing Method"
1. This stored procedure is free software (freeware) and can be used free of charge.
2. If you (whether you represent an individual or an entity) want to release the software or as a commercial use, the software (stored procedure/function) must be part of the functionality of your software,
This software (stored procedure) cannot be published and sold separately, nor can it be published as a software (stored procedure or function, etc.) that makes a similar function.
3. This authorization is authorized only to you (whether you represent an individual or an entity), and you cannot authorize another person to authorize and publish.

"Use Method"
Specific parameter Description Reference installation method inside the SQL statement

Get the pronunciation code of the name (GBK)
Select Name,dbo.fn_cncode (name,8) from table1 searches for all names containing records similar to ' small treasure ' pronunciation, without distinguishing between en Ch Sh and Z C S (GBK)
SELECT * FROM table1 where dbo.fn_cnlike (name, ' Xiao Bao ', 9) <>0
Get the pronunciation code of the name (BIG5)
Select Name,dbo.fn_cncode (name,0) from table1
Search for all names containing records similar to ' baby ' pronunciation, without distinguishing between ㄓㄔㄕ and ㄗㄘㄙ (BIG5)
SELECT * FROM table1 where dbo.fn_cnlike (name, ' Baby ', 1) <>0
"Installation Method"
1. Copy the dynamic link library VictorMsUa.dll to the Binn folder of MS SQL Server.
The default location is C:\Program Files\Microsoft SQL Server\mssql\binn
2. By executing SQL statements to register stored procedures, these stored procedures must be registered in the Master database.
Open the Master database, and then execute the following SQL statement
EXEC sp_addextendedproc ' Xp_getspcode ', ' VictorMsUa.dll '
EXEC sp_addextendedproc ' xp_gbksndlike ', ' VictorMsUa.dll '
EXEC sp_addextendedproc ' Xp_getchinesecode ', ' VictorMsUa.dll '
EXEC sp_addextendedproc ' xp_chinesesndlike ', ' VictorMsUa.dll '
3. Create the corresponding function in the user database
Open the user database, and then execute the following SQL statement
--GBK code, support for traditional Chinese and Simplified Chinese
--str1: Chinese character string, return value: Pinyin encoding (double spelling format)
CREATE FUNCTION Fn_getspcode (@str1 VARCHAR (8000))
RETURNS CHAR (1024) as
BEGIN
DECLARE @sp CHAR (1024)
EXEC Master.dbo.xp_getspcode @str1, @sp OUTPUT
Return @sp
End
Go
--GBK code, support for traditional Chinese and Simplified Chinese
--Sstr and Ssub for Chinese character strings, from the sstr inside according to the pronunciation of similar rules to find ssub
--options:0: General option, 1: Do not differentiate en Ch Sh with z C S, 2: Arbitrary order, 3: including 1 and 2 (GBK)
CREATE FUNCTION fn_gbksndlike (@sstr VARCHAR (8000), @ssub VARCHAR (8000), @options SMALLINT)
RETURNS SMALLINT as
BEGIN
DECLARE @v SMALLINT
EXEC master.dbo.xp_gbksndlike @sstr, @ssub, @options, @v OUTPUT
Return @v
End
Go
--Support BIG5 and GBK two kinds of encoding, support traditional Chinese and Simplified Chinese, set by options parameter
--str1: Chinese character string, return value: Pinyin encoding (double spelling format)
--options:0: BIG5 encoding, 8:GBK encoding
CREATE FUNCTION Fn_cncode (@str1 VARCHAR (8000), @options SMALLINT)
RETURNS CHAR (1024) as
BEGIN
DECLARE @sp CHAR (1024)
EXEC Master.dbo.xp_getchinesecode @str1, @options, @sp OUTPUT
Return @sp
End
Go
--Support BIG5 and GBK two kinds of encoding, support traditional Chinese and Simplified Chinese, set by options parameter
--Sstr and Ssub for Chinese character strings, from the sstr inside according to the pronunciation of similar rules to find ssub
--options:big5:0: General option, 1: Do not differentiate en Ch Sh with z C S, 2: Arbitrary order, 3: including 1 and 2 (GBK)
--Gbk:8: General option, 9: Do not differentiate en Ch Sh with z C S, 10: arbitrary order, 11: including 1 and 2 (GBK)
CREATE FUNCTION fn_cnlike (@sstr VARCHAR (8000), @ssub VARCHAR (8000), @options SMALLINT)
RETURNS SMALLINT as
BEGIN
DECLARE @v SMALLINT
EXEC master.dbo.xp_chinesesndlike @sstr, @ssub, @options, @v OUTPUT
Return @v
End
Go

Download MS SQL Server 2000 Chinese fuzzy Search stored procedure