-1

The following query is written using SQL Server 2014 CONTAINS function.

SELECT 
    Org1.OrganizationPK
    ,*
FROM Organization Org1
WHERE Org1.Deleted = 0
    AND CONTAINS (Org1.NAME,'"chris6*"')
    AND org1.OrgDomainInd = 1

But, the above query is NOT working.

I have followed this article to understand more about CONTAINS function. I have verified those examples as-they-are. Even here also, I am not getting any results.

I have verified this post from stackoverflow, but I could not apply for my requirement.

Hence, I am getting doubt whether I need to do anything more to configure SQL Server 2014 to work with CONTAINS fuction? Please let me know if there is anything I need to do to make SQL Server 2014 ready to use CONTAINS function. If there is nothing like that, please suggest me the solution.

Please don't suggest me to use LIKE operator. I am telling this why because, most of my colleagues suggested me same; hence, as a precautionary matter I am writing this statement here.

I am running behind my schedule to complete this task.

Community
  • 1
  • 1
Ashok kumar
  • 1,593
  • 4
  • 33
  • 68
  • Do you have a `full-text index` on the column you are searching in? CONTAINS is a predicate used in the WHERE clause of a Transact-SQL SELECT statement to **perform SQL Server full-text search on full-text indexed columns containing character-based data types.** (https://msdn.microsoft.com/en-us/library/ms187787.aspx) – Pred Oct 06 '15 at 14:21
  • @Pred: Yes, I have full-text-index on the required column. – Ashok kumar Oct 06 '15 at 14:24
  • In this case, can you please give us some sample data and the desired output? – Pred Oct 06 '15 at 14:25
  • Expected result is pretty clear. I need all the rows from Organization table those with Chris6 as part of Organization name. – Ashok kumar Oct 06 '15 at 14:32
  • 1
    Expected result is pretty clear for you who see the original data set and who knows the business requirements, but unfortunately the mind reading and ultimate hacking services on StackOverflow are under maintenance, so we can't use them to get more info. Providing the related DDLs, some sample data, your goal with expected output helps us to help you. "Not working" is a board description of a problem (no records returned, too many records returned, error occurred, the box exploded, etc), especially when we don't know the environment. – Pred Oct 06 '15 at 14:39

2 Answers2

0

I believe that contains functionality can only be used in tables configured to use/support Full Text Search -- an elderly feature of SQL Server that I have little experience with. If you are not using Full Text Search, I'm pretty sure contains will not work.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
0

Before CONTAINS will work against a column you need setup full text index. This is actually a different engine which runs as a separate service in SQL Server, so make sure it is installed an running.
Once you're sure the component is installed (it may already be installed) You need to do the following:

  1. Create a Full-Text Catalogue
  2. Create a Full-Text Index (you can have multiple of these in the same catalogue) against the tables/columns you want to be able to use full-text keywords
  3. Run a Population which will crawl the index created & populate the catalogue (these are seperate files which SQL Server needs to maintain in addition to mdf/ldf )

There's an ok tutorial on how to do this by using SSMS in SQL Server 2008 by Pinal Dave on CodeProject. SQL Server 2014 should be very similar. You can also perform these steps with TSQL:

  1. CREATE FULLTEXT CATALOG
  2. CREATE FULLTEXT INDEX
  3. ALTER FULLTEXT INDEX
Nick Kavadias
  • 7,542
  • 2
  • 37
  • 46
  • Hi Nick, Thank you for your answer. "Pinal Dave on Code" link is navigating to blank page. Can you please update this link with proper URL! – Ashok kumar Oct 07 '15 at 07:01