-2

Good day,

I'm having a problem when searching, say I want to find a certain university like for example, university of nowhere, if I search for university or nowhere then I get several suggestions including university of nowhere which is good. If I search for the exact wording then I get the exact result, BUT if I search for university nowhere i.e without the "of", then I get nothing..

Example SQL statement:

SELECT univ_name FROM university WHERE INSTR(univ_name,'--keyword here--')

Any suggestions on what is lacking on my sql statement?

Thanks in advance :)

jrran90
  • 668
  • 12
  • 18

2 Answers2

0

Sounds like you need the LIKE keyword:

SELECT univ_name FROM university WHERE univ_name LIKE '%University%;

% is a wildcard operator that will return the column where the text contains the word University

Darren
  • 68,902
  • 24
  • 138
  • 144
  • 1
    the answer should probably help OP with replacing any spaces in the original string with this char dynamically... – Randy Jun 23 '13 at 13:42
  • Does your data have it stored as `University` or `university`? It'll probably be case-sensitive. – Leigh Jun 23 '13 at 13:53
  • hmmm.. I think it's not case-sensitive, since when I tried searching **JUST** the keyword "university" it displays the result properly regardless if it's uppercase or lowercase.. but when I tried "university nowhere" then no result.. – jrran90 Jun 23 '13 at 14:01
0

It's going to be a little trickier than you might think.

Lets start with @searchTerm varchar(max)

What you need to do is dynamically build your where clause by splitting @searchTerm on the space and building it to look something like this:

where univ_name like '%term0%' AND univ_Name like '%term1%'

Refer to this question for the general strat on splitting strings in sql splitting strings

you store this in a varchar(max), lets say @sql

then set @sql = ''select * from university ' + @sql

finally run EXEC (@sql)

Community
  • 1
  • 1
nikolifish
  • 503
  • 1
  • 5
  • 18