Asp.net searches the database based on the first letter of Chinese Characters in pinyin (with the method of calling the LINQ function)

Method 1: first query all records and then query the records after the logical layer is converted to the first letter of the pinyin alphabet. It is obvious that dummies will do this.

Method 2: Add a field to the table to be searched to store the pinyin alphabet corresponding to the content of the field to be searched, and query the two fields at the same time during the search. This method is feasible, but it will increase the size of the database.

Method 3: Create a function in the database. Use this function to convert the query statement to search. The function is as follows:

CopyCode The Code is as follows: Set ansi_nulls on
Go
Set quoted_identifier on
Go
-- ===================================================== ======
-- Author: xoyozo
-- Create Date: 2010-4-17
-- Description: The first letter of a Chinese character.
-- ===================================================== ======
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' comment 'as word
Union all select 'B', n''
Union all select 'C', n' then'
Union all select 'D', N 'region'
Union all select 'E', n' then'
Union all select 'F', n' then'
Union all select 'G', n' then'
Union all select 'h', N 'hour'
Union all select 'J', n' else'
Union all select 'k', n' then'
Union all select 'l', n' then'
Union all select 'M', n' then'
Union all select 'n', n' then'
Union all select 'O', n' then'
Union all select 'P', n' exposure'
Union all select 'Q', n' then'
Union all select 'R', N 'region'
Union all select's ', N 'region'
Union all select 'T', n' then'
Union all select 'w', N 'hangzhou'
Union all select 'x', n' then'
Union all select 'y', n' then'
Union all select 'Z', n' else'
) 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

Usage:Copy codeThe Code is as follows: Select * from table where fun_getpy (field) Like n' % zgr %'

This method is quick and convenient.ArticleThe first letter of the search title in the table takes about three times less time to search by using this function. I believe that the cost of deploying a project with a small amount of data is very low.

operations in the LINQ mode
A beginner may be interested in. dbml files are not very familiar, and tables in the database can be dragged directly. on the left side of dbml, while stored procedures and functions can be dragged directly to the right of dbml, and then can be directly used in logic. The following is an example: copy Code the code is as follows: dataclasses1datacontext DB = new dataclasses1datacontext ();
var q = from D in dB. documents
where D. title. contains ("zgr") | dB. fun_getpy (D. title ). contains ("zgr")
select D;