April 12, 2017 at 5:39 am
Dear expert,
I would like to do a free text search on multiple columns.
I just want to find records based on words.
executing the following query does not return results while the table contains these words:
select * from table where contains(keywords, '"was"')
I know there is lots of logic (language specifc) in the freetext search functionality, is it possible to shut this down?
Using a query like below is an option but i not realy performing:
select * from table where keywords like '%was%'
Many thanks for your help.
Best regards.
April 12, 2017 at 5:47 am
You'll have to provide more details. The feature in SQL Server is known as FULL TEXT SEARCH. I don't know that it was ever designed to look at the entire record, but I have to admit that I'm not all that familiar with the details. In any case, you've provided very little detail to go on. Provide a full table CREATE statement for how you have things set up, including any setup you did for that feature, and then someone can provide assistance that is less speculative.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 12, 2017 at 6:47 am
Thank you for your quick response, I actualy ment FULL TEXT SEARCH.
Below you fill find a script which creates a database with a table and the freetext search catalog and index
As you see, some of them do not return rows but some of them do.
For example, if I search on the word was or here, it does not return records and I do want find these rows.
The configuration of SQL Server is a default configuration with the FREE TEXT SEARCH option enabled
Is this enough information?
/* Create a testdatabase */
CREATE DATABASE [TestDatabase]
GO
/* Create a testdatabase */
USE [TestDatabase]
/* Create a test table with 2 columns and a primary key*/
CREATE TABLE [dbo].[searchtable](
[primarykey] [nchar](10) NOT NULL,
[searchcolumn] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_searchtable] PRIMARY KEY CLUSTERED
(
[primarykey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/* Insert some records into the table */
INSERT INTO searchtable (primarykey, searchcolumn) VALUES ('1', 'red was here')
GO
INSERT INTO searchtable (primarykey, searchcolumn) VALUES ('2', 'green was here')
GO
INSERT INTO searchtable (primarykey, searchcolumn) VALUES ('3', 'black was here')
GO
/* Create a fulltext catalog */
CREATE FULLTEXT CATALOG [searchcatalog] AS DEFAULT
GO
/* Create a fulltext index on the table */
CREATE FULLTEXT INDEX ON searchtable(searchcolumn) KEY INDEX [PK_searchtable]
GO
It takes a short while before the index is created but afterwards, you can execute the following statements
/* This query finds no rows */
SELECT * from searchtable where contains(searchcolumn, 'was')
GO
/* This query finds no rows */
SELECT * from searchtable where contains(searchcolumn, '"was*"')
GO
/* This query finds one row */
SELECT * from searchtable where contains(searchcolumn, 'black')
GO
/* This query finds one row */
SELECT * from searchtable where contains(searchcolumn, '"black*"')
GO
/* This query finds no rows */
SELECT * from searchtable where contains(searchcolumn, 'here')
GO
/* This query finds no rows */
SELECT * from searchtable where contains(searchcolumn, '"here*"')
GO
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply