CODEWITHSUNDEEP
sql-serverviewfull-text-search
Mike_Device
Mike_Device

Reputation: 672

Full-Text Search by last name, first name and second name

Hello I use SQL Server and I want to make a Full-Text Search in folllow table

CREATE TABLE Students
(
    id int PRIMARY KEY IDENTITY,
    firstName varchar(MAX) NOT NULL CHECK (firstName != ''),
    secondName varchar(MAX) NULL CHECK (secondName != ''),
    lastName varchar(MAX) NOT NULL CHECK (lastName != ''),
    birthDate datetime2 NOT NULL CHECK (birthDate >= '1.01.1990'),
    recordBookNumber int NOT NULL UNIQUE CHECK (recordBookNumber > 0),
    enrollmentDate datetime2 NOT NULL CHECK (enrollmentDate >= '1.01.1990'),
    groupId int NOT NULL FOREIGN KEY REFERENCES [Groups](id) ON DELETE CASCADE
)

So I've created view StudentsSearchView, Clustered Index, Fulltext catalog and Fulltext Index

Fulltext Index is here:

CREATE FULLTEXT INDEX ON
    dbo.StudentsSearchView (
        firstName LANGUAGE 1033,
        secondName LANGUAGE 1033,
        lastName LANGUAGE 1033
    )
KEY INDEX IDX_SEARCH ON ftCatalog
WITH (
    CHANGE_TRACKING = AUTO, 
    STOPLIST = SYSTEM
)

So I have a textbox, where I input string like 'John Peter Smith' and I want to get all matches, but I can't to fint out what query I need to use for it. I try this

SELECT * FROM
    dbo.StudentsSearchView
WHERE CONTAINS (
    *, '"LastName*" & "FirstName*" & "SecondName*"'
)

But nothing found, although I have in table this row.

P.S. LastName, FirstName and SecondName will insert programmatically

Upvotes: 1

Views: 1488

Answers (1)

Mike_Device
Mike_Device

Reputation: 672

I solved. My mistake is View. It was

CREATE VIEW
    dbo.StudentsSearchView WITH SCHEMABINDING
AS
SELECT
    id, firstName, secondName, lastName
FROM
    dbo.Students
GO

But It needs to be like this:

CREATE VIEW
    dbo.StudentsSearchView (id, Fio) WITH SCHEMABINDING
AS
SELECT
    id, firstName + ' ' + ISNULL(secondName, '') + ' ' + lastName
FROM
    dbo.Students
GO

So, now this Select query works

SELECT * FROM
    dbo.StudentsSearchView
WHERE CONTAINS (
    *, '"LastName*" & "FirstName*" & "SecondName*"'
)

Upvotes: 1

Related Questions