63 lines
2.8 KiB
Transact-SQL
63 lines
2.8 KiB
Transact-SQL
/*
|
|
EXEC sp_fulltext_database 'enable'
|
|
go
|
|
CREATE FULLTEXT CATALOG FT_Catalog_JSJ
|
|
go
|
|
--DROP FULLTEXT INDEX ON CONTENTS
|
|
GO
|
|
CREATE FULLTEXT INDEX ON Contents
|
|
(
|
|
Text
|
|
Language 1033
|
|
)
|
|
KEY INDEX PK_Contents ON FT_Catalog
|
|
WITH CHANGE_TRACKING AUTO
|
|
GO
|
|
--DROP FULLTEXT INDEX ON Documents
|
|
CREATE FULLTEXT INDEX ON Documents
|
|
(
|
|
DocAscii
|
|
Language 1033
|
|
)
|
|
KEY INDEX PK_Documents ON FT_Catalog
|
|
WITH CHANGE_TRACKING AUTO
|
|
GO
|
|
*/
|
|
SELECT * FROM contents WHERE CONTAINS(text, '"FOLLOW*"') -- prefix Follow
|
|
SELECT * FROM contents WHERE CONTAINS(text, '"RCP*"') -- prefix Follow
|
|
SELECT * FROM contents WHERE CONTAINS(text, '"FOLLOW"') -- exact Follow
|
|
SELECT * FROM contents WHERE CONTAINS(text, 'PORV AND SG') -- boolean
|
|
SELECT * FROM contents WHERE CONTAINS(text, 'PORV NEAR SG') -- boolean
|
|
SELECT * FROM contents WHERE CONTAINS(text, '"SG AND PORV"') -- boolean
|
|
SELECT * FROM contents WHERE CONTAINS(text, '"SG*" AND "PORV*"') -- boolean
|
|
SELECT * FROM contents WHERE CONTAINS(text, '"SG" AND "PORV"') -- boolean
|
|
SELECT * FROM contents WHERE CONTAINS(text, '"SG*" AND "PORV*"') -- boolean
|
|
AND NOT CONTAINS(text, '"SG" AND "PORV"') -- boolean
|
|
SELECT * FROM contents WHERE CONTAINS(text, '"SG PORV"') -- boolean
|
|
SELECT * FROM contents WHERE text like '%SG PORV%' -- SQL Search
|
|
|
|
SELECT * FROM contents WHERE CONTAINS(text, 'FORMSOF(INFLECTIONAL, "foot")'); -- inflectional foot
|
|
SELECT * FROM contents WHERE CONTAINS(text, '"run*"'); -- prefix run
|
|
SELECT * FROM contents WHERE CONTAINS(text, 'FORMSOF(INFLECTIONAL, "run")'); -- inflectional run
|
|
SELECT * FROM contents WHERE CONTAINS(text, '"run*"')
|
|
AND NOT CONTAINS(text, 'FORMSOF(INFLECTIONAL, "run")'); -- prefix run and not inflectional run
|
|
SELECT * FROM contents WHERE CONTAINS(text, '"vacuum decrease*"'); -- prefix vacuum decrease
|
|
|
|
SELECT count(*) DG_or_DieselGenerator FROM contents where text like '%DG%' OR text like '%DIESEL GENERATOR%'
|
|
SELECT count(*) DG FROM contents where text like '%DG%'
|
|
SELECT count(*) DieselGenerator FROM contents where text like '%DIESEL GENERATOR%'
|
|
SELECT * FROM contents where text like '%DIESEL GENERATOR%'
|
|
|
|
SELECT * FROM contents WHERE CONTAINS(text, 'FORMSOF(THESAURUS, "DG*")'); --
|
|
SELECT * FROM contents WHERE CONTAINS(text, 'FORMSOF(THESAURUS, "DIESEL GENERATOR")'); --
|
|
SELECT * FROM contents WHERE FREETEXT(text, '"DIESEL GENERATOR*"'); --
|
|
SELECT * FROM contents WHERE FREETEXT(text, '"DG*"'); --
|
|
SELECT * FROM contents WHERE CONTAINS(text, 'FORMSOF(THESAURUS, "SG")'); --
|
|
SELECT * FROM contents WHERE CONTAINS(text, 'FORMSOF(THESAURUS, "STEAM GENERATOR")'); --
|
|
|
|
EXEC sp_fulltext_load_thesaurus_file 1033;
|
|
SELECT * FROM contents WHERE CONTAINS(text, '"RUN*" and "PUMP*"'); --
|
|
SELECT * FROM contents WHERE CONTAINS(text, 'FORMSOF(INFLECTIONAL, "run") and "PUMP*"'); --
|
|
SELECT * FROM contents WHERE CONTAINS(text, '"RUN*" and "PUMP*"') --
|
|
AND NOT CONTAINS(text, 'FORMSOF(INFLECTIONAL, "run") and "PUMP*"'); --
|