Click here to Skip to main content
11,576,977 members (59,038 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET
I have written this query in storeprocedure.But using this I can only search data with complete text.For Example:I can search data whose name='ronak'.i can not search data whose name start with 'r'.

please help me how to do this.


SELECT profile.name1 AS name, profile.address, profile.mob_no, profile.emailid, login.username
FROM profile INNER JOIN
login ON profile.log_id = login.log_id
WHERE (profile.name1 LIKE @name1) OR
(profile.mob_no LIKE @mob_no) OR
(profile.emailid LIKE @emailid) OR
(login.username LIKE @username)

I have try below 2 solution ,but when I write query


SET @name1 = '%' + @name1 + '%'

SELECT profile.name1 AS name, profile.address, profile.mob_no, profile.emailid, login.username
FROM profile INNER JOIN
login ON profile.log_id = login.log_id
WHERE (profile.name1 LIKE @name1) OR
(profile.mob_no LIKE @mob_no) OR
(profile.emailid LIKE @emailid) OR
(login.username LIKE @username)
then it is working for name textbox.And when I write query

SET @name1 = '%' + @name1 + '%'
SET @mob_no = '%' + @mob_no + '%'
SET @emailid = '%' + @emailid + '%'
SET @username = '%' + @username + '%'

SELECT profile.name1 AS name, profile.address, profile.mob_no, profile.emailid, login.username
FROM profile INNER JOIN
login ON profile.log_id = login.log_id
WHERE (profile.name1 LIKE @name1) OR
(profile.mob_no LIKE @mob_no) OR
(profile.emailid LIKE @emailid) OR
(login.username LIKE @username)
then it is not working for any textbox.
Posted 28-Dec-12 3:36am
Edited 28-Dec-12 5:43am
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try:

SELECT profile.name1 AS name, profile.address, profile.mob_no, profile.emailid, login.username
FROM profile INNER JOIN
login ON profile.log_id = login.log_id
WHERE     
(profile.name1 LIKE @name1 +'%') OR
(profile.mob_no LIKE @mob_no +'%') OR
(profile.emailid LIKE @emailid +'%') OR
(login.username LIKE @username +'%')
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

If you want to search for any inclusion in a value then you just need to wrap your argument with '%' characters.
SET @name1 = '%' + @name1 + '%'
SET @mob_no = '%' + @mob_no + '%'
SET @emailid = '%' + @emailid + '%'
SET @username = '%' + @username + '%'
 
SELECT profile.name1 AS name, profile.address, profile.mob_no, profile.emailid, login.username
  FROM profile INNER JOIN
          login ON profile.log_id = login.log_id
 WHERE profile.name1 LIKE @name1 
    OR profile.mob_no LIKE @mob_no
    OR profile.emailid LIKE @emailid
    OR login.username LIKE @username
  Permalink  

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

  Print Answers RSS
0 DamithSL 155
1 Sergey Alexandrovich Kryukov 131
2 OriginalGriff 115
3 Afzaal Ahmad Zeeshan 95
4 Abhinav S 90
0 OriginalGriff 820
1 Sergey Alexandrovich Kryukov 631
2 Abhinav S 528
3 F-ES Sitecore 420
4 Suvendu Shekhar Giri 365


Advertise | Privacy | Mobile
Web03 | 2.8.150603.1 | Last Updated 28 Dec 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100