April 19, 2014 at 6:07 pm
Hello, I have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)
Query
DECLARE @searchString nvarchar(255);
DECLARE @searchStringWild nvarchar(275);
SET @searchString = 'first';
SET @searchStringWild = UPPER('"' +@searchString+ '*"');
SELECT id, '...' + SUBSTRING(searchResults.MatchedCell, searchResults.StartPosition, searchResults.EndPosition - searchResults.StartPosition) + '...' as Result
FROM (
SELECT Id,
MatchPosition = Number - LEN(@searchString),
CASE WHEN Number - LEN(@searchString) - 50 > 0
THEN Number - LEN(@searchString) - 50 ELSE 0 END as StartPosition,
CASE WHEN Number - LEN(@searchString) + 50 < LEN(MatchedCell)
THEN Number - LEN(@searchString) + 50 ELSE LEN(MatchedCell) END as EndPosition,
MatchedCell
FROM
(
SELECT Id,
TextColumn as MatchedCell,
Number = matchIndex.Number
FROM
Sample.dbo.SampleTable
AS matchedCells
CROSS APPLY
(
SELECT
Number
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY [object_id])
FROM
sys.all_objects
)
AS n(Number)
WHERE
Number > 1
AND Number <= CONVERT(INT, LEN(matchedCells.TextColumn))
AND SUBSTRING(@searchString + matchedCells.TextColumn, Number, LEN(@searchString)) = @searchString
AND CONTAINS (matchedCells.TextColumn, @searchStringWild)
) as matchIndex
) as matchPositions
) as searchResults
Here is the sample table and data
CREATE TABLE [dbo].[SampleTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TextColumn] [nvarchar](1000) NOT NULL,
CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[SampleTable] ON
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (1, N'This is the first Test string')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (2, N'This is the second one')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (3, N'This is the first really long string of text that should be included in the result set.')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (4, N'This is the second long string that will not be returned in the result set')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (5, N'This is a really really long result set that should also be first included in the result set. It has a seperate sentence in it as well.')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (6, N'Now this is a really really first one. It is so long that I have forgotten how long it really was. Well it could be really long but first lets do this. ')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (7, N'
Hello,
This is a really long first string with html inside
This is another line
This is a first line
This one is another line that is really really long, first
First, !=First
I want to see if it will find all the first lines.
THIS IS A CAPITAL LETTER LINE, THE LINE WILL SKIP A LINE
HERE IS THE FIRST LINE ')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (8, N'
This markup is copies from Microsoft Word Firstly
This Has some tabs.
First I want to know it it can count them.
<b>The counts should return 4 firsts first</b>
Well Maybe five first.')
SET IDENTITY_INSERT [dbo].[SampleTable] OFF
April 20, 2014 at 5:25 am
If the search string is only one word/pattern then a recursive approach could be an option. Or maybe a combination of the two?
😎
DECLARE @searchString nvarchar(255)= 'first';
DECLARE @DISP_SIZE INT = 50;
;WITH POST_TABLE AS
(
SELECT
ST.Id
,ST.TextColumn
,CHARINDEX(@searchString,ST.TextColumn,1) AS SPOS
FROM dbo.SampleTable ST
WHERE CHARINDEX(@searchString,ST.TextColumn,1) > 0
UNION ALL
SELECT
ST.Id
,ST.TextColumn
,CHARINDEX(@searchString,ST.TextColumn,ST.SPOS + 1) AS SPOS
FROM POST_TABLE ST
WHERE CHARINDEX(@searchString,ST.TextColumn,ST.SPOS + 1) > 0
)
SELECT
Id
,SPOS
,'... ' + SUBSTRING(TextColumn, CASE
WHEN SPOS - (@DISP_SIZE / 2) <= 0 THEN 1
ELSE SPOS - (@DISP_SIZE / 2)
END, @DISP_SIZE) + ' ...' AS SAMPLE_STRING
FROM POST_TABLE;
April 20, 2014 at 5:58 am
maybe this article by Phil Factor will provide some ideas for you....
interesting read anyway.
https://www.simple-talk.com/sql/t-sql-programming/searching-for-strings-in-sql-server-databases-/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply