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
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.
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.
The 'Contains' operator adds a wild character automatically to the search string, while the 'Like' operator matches for the exact search string.
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).
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 .
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With