By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

query to search even when user skips in between text

100+
P: 232
i have a database as
table name school
field name location
eg in location column data
green school,tagore garden,chink road,jammu
i want to make search on location such that when user enter
green
green school, tagore
search words should come continous
problem
on skipping in between text
eg on entering
green school, jammu
record not show
search query is

select*from school where location like'%a%'
plz suggest me query to search even when user skips in between text
Feb 4 '09 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
Use the PATINDEX() function.

-- CK
Feb 4 '09 #2

100+
P: 232
@ck9663
no patindex is also not working when user skips in between text
Feb 4 '09 #3

ck9663
Expert 2.5K+
P: 2,878
Tweak this code


Expand|Select|Wrap|Line Numbers
  1. declare @str as varchar(100)
  2. declare @str1 as varchar(50)
  3.  
  4. set @str = 'green school,tagore garden,chink road,jammu'
  5. set @str1 = 'tagore garden,jammu'
  6. set @str1 = '%' + replace(@str1,',','%') +'%'
  7. select patindex(@str1, @str)
  8.  
  9.  
  10. set @str = 'green school,tagore garden,chink road,jammu'
  11. set @str1 = 'green school,jammu'
  12. set @str1 = '%' + replace(@str1,',','%') +'%'
  13. select patindex(@str1, @str)
  14.  
-- CK
Feb 4 '09 #4

100+
P: 232
@ck9663
thank u so much for your guidance now its working
Feb 5 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.