How can we search data alphabetially order & in middle of the data.

Question

0.00/5 (No votes)

See more:

SQL

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.

Posted 14-May-13 21:57pm

Mas11

Updated 15-May-13 18:38pm

v3

Add a Solution

Comments

Jignesh Khant 15-May-13 4:25am

use 'like' operator of sql like this
SELECT * FROM tbl WHERE ProductName LIKE 'j%';

Mas11 15-May-13 4:48am

Please read my question properly !

[no name] 15-May-13 8:51am

Is it "FULL TEXT SEARCH" which u r looking for..??

Mas11 16-May-13 0:36am

No, Is it not full text search. It is, If user write single letter like 'j' as above 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 get these word.

2 solutions

Solution 1

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

Posted 14-May-13 22:45pm

Anil.Adari

Comments

Mas11 16-May-13 0:39am

Thanks for your answer but its not that one which I m looking for.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

abdussalam143 · Accepted Answer · 2013-05-15T21:15:00

May This Help You

SQL

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

SQL

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

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

SQL

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

I Check Length of Key Word in

SQL

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.

SQL

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

or any other Column