Shulou(Shulou.com)05/31 Report--

This article introduces how to optimize the abuse of temporary tables and sorting in SQL database. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Cursors, temporary tables, triggers, COLLATE, etc.

It is understandable that these are all good things. Why should I take the time to write these things today?

Because I found that many people slowly use these things for a long time and form a habit of moving them out no matter what problems they solve, so I see a lot of beautiful code that doesn't look so good in terms of performance efficiency.

All right, let's cut the crap and get down to business.

Today's case.

Scene:

You need to search for the user by the name keyword entered by the user. The user enters the keyword'x' to search for the user (data comes from table [Name field] or memory [List])

Request:

The order of the results should be:

X

Xia

Xiao

Yx

That is:

Results that include the letter x should be displayed.

The result of the first letter match should be at the top of the list (such as the beginning of x)

Shorter results should be ranked first under the same condition 2 (such as x ahead of xia)

Can you give me a solution of C # and SQL Server (2008)?

Add:

It is best if you can solve Chinese problems together, such as searching for'x'.

The order of the results should be:

X

Xiani

Xia Rong

Xiao Xiaoxiao

Yang Xing

To include the first letter of pinyin of Chinese characters, I wonder if SQL Server supports the search of this feature?

Thank you [the pace of learning] for the questions raised by this netizen

In fact, it is not difficult to solve this problem, it is nothing more than the conversion of Chinese characters to the first letter of Pinyin.

-

Give the first solution first.

-preparation begins--

If object_id ('zhuisuos') is not null

Drop table zhuisuos

Go

Create table zhuisuos

(

Name varchar (100)

)

Insert into zhuisuos values ('recourse')

Insert into zhuisuos values ('recourse 2')

Insert into zhuisuos values ('xia')

Insert into zhuisuos values ('dxc')

Insert into zhuisuos values ('x')

Insert into zhuisuos values ('xx')

Insert into zhuisuos values ('xiani')

Insert into zhuisuos values ('yx')

Insert into zhuisuos values ('Xia Rong')

Insert into zhuisuos values ('Xiao')

Insert into zhuisuos values ('Yang Xing')

Go

-- establishing the first letter function of converting Chinese characters to Pinyin

If object_id ('fn_getpy1') is not null

Drop function fn_getpy1

Go

GO

Create function [dbo] .fn _ getpy1

(@ str nvarchar (4000))

Returns nvarchar (4000)

As

Begin

Declare @ str_len int,@result nvarchar 4000

Declare @ zhuisuo table

(firstspell nchar (1) collate Chinese_PRC_CI_AS

Letter nchar (1))

Set @ str_len=len (@ str)

Set @ result=''

Insert into @ zhuisuo

(firstspell,letter)

Select'A','A 'union all select' eight','B 'union all

Select 'ZAM','C 'union all select' union all select','D 'union all

Select 'hair','E 'union all select' hair','F 'union all

Select 'union all select','G 'hafnium','H 'union all

Select'no','J 'union all select' cut','K 'union all

Select 'union all select','L 'union all','M'

Select 'union all select','N 'Ooh','O 'union all

Select 'union all select','P 'seven','Q 'union all

Select 'union all select','R 'three','S 'union all

Select'he','T 'union all select' him','W 'union all

Select'Xi','X 'union all select' ya','Y 'union all

Select 'son','Z'

While @ str_len > 0

Begin

Select top 1 @ result=letter+@result,@str_len=@str_len-1

From @ zhuisuo

Where firstspell

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.