How to Show a Certain Word from a Field? 2

[berkshirea]

Hi guys,

I have a search textfield in my site and I am thinking of putting a "google-suggest-like" feature. Can you please give me some tips. I have 2 unrelated tables to search.
e.g.

Table1
------------
ID thing1
------------
1 bike
2 car
3 train
4 plane
------------


Table2
-------------------------------
ID | field2 | field3
-------------------------------
1 | a bike is fast | That biker is cool
2 | ferrari is faster| expensive
| than other cars |
3 | japan's bullet | sweet
| train is fast |
4 | using plane is | they are indeed.
| the fastest mode


When I used the Table2 and typed the word "fast" for my google-suggest-like search textfield, the dropdown has these entries:

a bike is fast
ferrari is faster than other cars
japan's bullet train is fast
using plane is the fastest mode


I want to see the dropdown to only have these entries:
fast
faster
fastest

Any ideas guys?

Thanks

 

[djj55]

Does your query use '% fast%' (notice the space after first percent sign)?
djj

 

[berkshirea]

no. just '%fast%'

 

[sqlfable]

What you are attempting is something much more complex than a simple LIKE. I recommend checking code examples on the net regarding combination usage of PATINDEX and SUBSTRING in TSQL.

First couple google hits showed this and this

 

[berkshirea]

Hi sqlfable,

Thanks for pointing me to the right direction.

SUBSTRING and PATINDEX did it!!!

tnx