Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Use of contains() in sql server

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

like image 475
user1926138 Avatar asked Jun 14 '13 06:06

user1926138


People also ask

How use contains operator in SQL?

The CONTAINS operator must always be followed by the > 0 syntax, which specifies that the score value calculated by the CONTAINS operator must be greater than zero for the row to be selected.

How do you write a Contains statement in SQL?

The SQL CONTAINS function for Oracle database The basic syntax looks like this: CONTAINS ( column_name, substring, label ); The column_name and substring parameters are the same as they are with SQL Server. Column_name is the column you are searching and substring is the string you are searching for.

What is difference between contains and like in SQL?

The 'Contains' operator adds a wild character automatically to the search string, while the 'Like' operator matches for the exact search string.

How do I check if a string contains in SQL?

Method 1 - Using CHARINDEX() function This function is used to search for a specific word or a substring in an overall string and returns its starting position of match. In case no word is found, then it will return 0 (zero).


1 Answers

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 .

like image 68
Hiren Dhaduk Avatar answered Oct 27 '22 07:10

Hiren Dhaduk