Use of contains() in sql server

Question

I am having a table name Product contains 3 columns as:

Product-id
name
Price

In name column, all product names are present.

Example: 1340 GPS, 1340T GPS etc.

When I write

select top 10 * from product where contains(name,'1340');

Result 1 row 1340 GPS

but when I search

select top 10 * from product where name like '%1340%';

Then the result is 1340 GPS and 1340T GPS.

Actually my requirement is I will use contains() but it has to show both the rows like LIKE operator.

How to do so :?

Please help

Thanks in advance

Extravascular

why do you insist using contains() ? – Guerrero

i need full text search – Extravascular

is there any way to solve above issue or its impossible??????? – Extravascular

the above query i am using to searching a product. If i use like operator it will take more time then contains() and also i need full text search.So i need Contains() my point of view. – Extravascular

I am using number of inner and other joins in my query. Is there any other way to solve this one then welcome – Extravascular

@Extravascular 1340 come always in prefix or it can come in suffix or anywhere – Propend

Answer 1

Here is straight way to do this . you can use "*" before in contain syntax same as like operator . but you need to use double quote before and after the search string . check following query :

SELECT *
FROM product
WHERE CONTAINS(name,'"*1340*"');

it will definitely work .

Phenol

Not need to use * in full text search. – Clearheaded

Just a note: You can't do a prefix of * with Contains. It only works in this example because 1340 is the start of the word. If it was T1340 for instance, this wouldn't work. – Teresaterese

its not working , its show no record when use contains() – Towne

Answer 2

select * from product where FREETEXT (product.,'"1340"')

Marqueritemarques