searching problem i hope somebody will help me here

[lenerd3000]

hello,

 

i have created a query that is to search text in my database and it goes well. but my friend the other day is complaining that he did not find what he search in the database but actually the data is really in there.. the data contains ampersand(&) like 'tom & jerry' and also a text 'mcs of tampa inc'.

my query is this:

 

select * from mytable where mytext like '%mcs of tampa inc%'

select * from mytable where mytext like '%tom & jerry%'

 

in the first query, it runs ok when the text is 'mcs of tampa' but if i complete it to 'mcs of tampa inc' no results has been return.

 

is theres a special characters their? pls tell me... thank you

[fnairb]

Try the second query again but replace the & with %.  There is a good chance that the ampersand was encoded when it was inserted into the database.

 

select * from mytable where mytext like '%tom % jerry%'

 

Another possibility is that case is your problem.  "Tom" is not the same as 'tom'.  Consider making both the values lower case in the search.  Yes the second lower() is redundant but I'm expecting that the string will be replaced with a variable in your code.

 

select * from mytable where lower(mytext) like lower('%tom % jerry%')