/ * - NTEXT search
Press Keyword in the TB table to find Content in TA lists the specific location of each keyword in Content - Zou Jian 2004.07 (please keep this information) - * /
- Test data Create TABLE TA (ID Int Id Id IDENTIN) INSERT TA SELECT 'I am Chinese, I am Chinese' Union All Select 'Chinese people love China Chinese people love China Chinese people love China China People love China '
Create Table TB (Keyword Nvarchar (100)) Insert Tb SELECT 'Chinese' Goion All Select 'China' Go
/ * ================= Processing ================================= * / if EXISTS (SELECT * from dbo.sysObjects where id = Object_id (n '[order table]') And ObjectProperty (ID, n'susertable ') = 1) DROP TABLE [Sequence Table] GO
- For efficiency, so a auxiliary table to cooperate with SELECT TOP 4000 ID = Identity Table from syscolumns a, syscolumns balTer Table Sequence table add constraint pk_id_ 数 Table Primary Key (ID) GO
- Create a process of stored procedure Create Proc P_searchaseScreate Table #t (ID INT, Keyword Nvarchar (100), POSITION INT)
Declare @s nvarchar (4000), @ keyword nvarchar (100) Declare @ID INT, @ i int, @ ilen int
DECLE TB CURSOR LOCAL FORSELECT A.ID, B.Keyword, Position = Charindex (B.Keyword, A.Content) -1, Ilen = 4000-Len (B.Keyword) from Ta A, TB Bwhere Charindex (B.Keyword, a.content> 0
Open TBFETCH TB INTO @ id, @ @ kyword, @ i, @ ilenwhile @@ fetch_status = 0begin select @ s = substring (content, @ i 1,4000) from ta where id = @ id while @S <> 'Begin Insert #t (ID, keyword, position) select @ id, @ keyword, ID @ i from order table where charIndex (@ keyword, @ s, id) = id
SELECT @ i = @ i @ i, @ s = substring (content, @ i 1,4000) from ta where id = @ id end fetch tb @ iD, @ Keyword, @ i, @ ilnendclose tbdeallocate TBSELECT * FROM # # TGO - Call Example EXEC P_SEARCHGO
- Remove Test DROP TABLE Sequence Table, TA, TBDROP Proc P_Search
/*--Test Results
ID Keyword Position ---------------------------- 1 in China 31 China 31 China 82 in 12 in 62 in 92 in 142 China 122 China 12 China 62 China 92 China 142 China 172 China 222 China 252 China 30
(The number of rows affects is 20 lines) - * /
