我们如何按字母顺序搜索数据&在数据中间。 [英] How can we search data alphabetially order & in middle of the data.

问题描述

假设我有表 '  tbl'，其列 '  ProductName'  多条记录。现在我希望 在中搜索 text  方式用户 类型 '  j'    每个 case 它显示从 j字母开始的ProductName（我们可以喜欢 condition '  j％'）但 时用户 类型 ' 牛仔裤 然后它显示的ProductName有牛仔裤字'  Jeans'，'  Levis Jeans的，'  Denim Kids Jeans'，'  Flat Jeans New'（请参阅下文 table 结构）。我在中显示这些搜索 text   '  textbox' 使用   Ajax AutoCompleteExtender'控件。所以所有搜索 text 在前端显示。 
 
我的要求：如果  user 写单个字母 like  '  j'  as 下面提到的示例，然后 所有搜索到来的是'  j'字母。但是如果  user 写一些正确的单词< span class =code-keyword> like （'  jeans'，' 电话'）然后显示结果  的基础   全部 列 其中获取这些字词。 
 
 ID ProductName 
 
  1 手机
  2 鞋子
  3 牛仔裤
  4  Jug 
 < span class =code-digit> 5 礼品
  6 艺术
  7  Levis Jeans 
  8 牛仔布儿童牛仔裤
  9  IPhone新手机
  10 平牛仔裤新
 
 案例 1：现在如果  user 只写'  j' 然后它显示结果喜欢 
 
 ID ProductName 
 
  3 牛仔裤
  4  Jug 
 
 案例 2：但如果 用户写'  jeans' 然后显示结果喜欢 
 
 ID ProductName 
 
  3 牛仔裤
  7  Levis Jeans 
  8 牛仔布儿童牛仔裤
  10 平牛仔裤新
 
 或 如果 用户写'  phone' < span class =code-keyword>然后它显示结果喜欢 
 
 ID ProductName 
 
  1 手机
  9  IPhone新手机

任何建议都非常感谢。

解决方案

May This帮助您

 创建  PROC  [dbo]。[Product_Search] 
（ @ KeyWord   AS  < span class =code-keyword> VARCHAR （MAX））
  AS  
 
  DECLARE   @ qSel   AS   VARCHAR （MAX）
  DECLARE   @ qWhere   AS   VARCHAR （MAX）
  DECLARE   @ query   AS   VARCHAR （MAX）
 
 
  SET   @ qSel  = '  SELECT * FROM [Products] cc' 
 
  SET   @ qWhere  = ' ' 
 
 
  IF  LEN（ @ KeyWord ）>  1  
  BEGIN  
  SET   @ qWhere  =  @ qWhere  + '  cc.ProductName类似''％' +  @ KeyWord  + ' ％''' 
  END  
  ELSE  
  BEGIN  
  SET   @ qWhere  =  @ qWhere  + '  cc.ProductName类似''' +  @ KeyWord  + '  ％''' 
  END  
 
 
 
  IF  LEN（ @ qWhere ）！=  0  
  SET   @ qWhere  = '  WHERE' +  @ qWhere  
 
 
  SET   @ query  =  @ qSel  +  @ qWhere  
 
  PRINT （ @ query  ）
  EXEC （ @ query ）

如果你输入''j''那么结果将基于这个查询

  SELECT  *  FROM  [Products] cc  WHERE  cc.ProductNam e 赞 '  j％' 

如果你输入''牛仔裤''那么结果将基于这个查询

  SELECT  *  FROM  [产品] cc  WHERE  cc.ProductName 赞 ' ％jeans％' 

我在

  IF  LEN（ @ KeyWord ）>  1  

如果需要，可以根据需要进行更改。如果你想改变以下行，也可以添加order by子句。

  SET   @ query  =  @ qSel  +  @ qWhere  + '  cc.ProductName ASC'的订单 

或任何其他列

一旦看到它可能为您使用的链接.....

ASP.NET中排名搜索的实现示例 [ ^ ]

Suppose  I have a table 'tbl' which have column 'ProductName' with multiple records. Now I want to search text in this way that user type 'j' then in every case it show ProductName which start from j letter (we can do like condition 'j%')  but when user type 'Jeans' then it show ProductName which have jeans word 'Jeans', 'Levis Jeans', 'Denim Kids Jeans', 'Flat Jeans New' (Please see below table structure).I am showing these searching text in 'textbox' using 'Ajax AutoCompleteExtender' control. So all searching text show in frontend.

What my Requirement : If user write single letter like 'j' as below mentioned example, then all the search comes which has 'j' letter first. But if user write some proper word like ('jeans','Phone') then it show result on the basis of these word in all the column where it gets these word.

ID   ProductName

1    Cell Phone
2    Shoes
3    Jeans
4    Jug
5    Gift
6    Art
7    Levis Jeans
8    Denim Kids Jeans
9    IPhone new handset
10   Flat Jeans New  

 Case 1: Now If user write only 'j' then it show result like 

ID   ProductName

3    Jeans
4    Jug

 Case 2: but If user write 'jeans' then it show result like

ID   ProductName

3    Jeans
7    Levis Jeans
8    Denim Kids Jeans
10   Flat Jeans New  

or If user write 'phone' then it show result like

ID   ProductName

1    Cell Phone
9    IPhone new handset

Any Suggestion really appreciate.

解决方案

May This Help You

CREATE  PROC[dbo].[Product_Search] 
(@KeyWord AS VARCHAR(MAX))
AS

DECLARE @qSel    AS VARCHAR(MAX)
DECLARE @qWhere  AS VARCHAR(MAX)
DECLARE @query   AS VARCHAR(MAX)


SET @qSel = 'SELECT * FROM [Products] cc '

SET @qWhere = ' '

	
IF LEN(@KeyWord) > 1
BEGIN
    SET @qWhere = @qWhere + 'cc.ProductName  Like ''%' + @KeyWord + '%'' '
END
ELSE
BEGIN
    SET @qWhere = @qWhere + 'cc.ProductName  Like ''' + @KeyWord + '%'' '
END		



IF LEN(@qWhere) != 0
    SET @qWhere = 'WHERE ' + @qWhere	
	
	
SET @query = @qSel + @qWhere 

PRINT (@query)
EXEC (@query)

if you enter ''j'' then the result will base on this query

SELECT * FROM [Products] cc WHERE  cc.ProductName  Like 'j%' 

if you enter ''jeans'' then the result will base on this query

SELECT * FROM [Products] cc WHERE  cc.ProductName  Like '%jeans%' 

I Check Length of Key Word in

IF LEN(@KeyWord) > 1

you can change it if you want it depends on your requirement . can also add order by clause if you want by changing the following line.

SET @query = @qSel + @qWhere + ' order by cc.ProductName ASC '

or any other Column

once see the link it may be use full for you.....
Implementation Example of Ranked Search in ASP.NET[^]

这篇关于我们如何按字母顺序搜索数据&在数据中间。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！