StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Expanding on Ishmael's idea, it's not the final solution, but I think it's a good way to start. </p> <p>Firstly we need to get the list of words that have been retrieved with the full-text engine: </p> <pre><code>declare @SearchPattern nvarchar(1000) = 'FORMSOF (INFLECTIONAL, " ' + @SearchString + ' ")' declare @SearchWords table (Word varchar(100), Expansion_type int) insert into @SearchWords select distinct display_term, expansion_type from sys.dm_fts_parser(@SearchPattern, 1033, 0, 0) where special_term = 'Exact Match' </code></pre> <p>There is already quite a lot one can expand on, for example the search pattern is quite basic; also there are probably better ways to filter out the words you don't need, but it least it gives you a list of stem words etc. that would be matched by full-text search.</p> <p>After you get the results you need, you can use RegEx to parse through the result set (or preferably only a subset to speed it up, although I haven't yet figured out a good way to do so). For this I simply use two while loops and a bunch of temporary table and variables:</p> <pre><code>declare @FinalResults table while (select COUNT(*) from @PrelimResults) > 0 begin select top 1 @CurrID = [UID], @Text = Text from @PrelimResults declare @TextLength int = LEN(@Text ) declare @IndexOfDot int = CHARINDEX('.', REVERSE(@Text ), @TextLength - dbo.RegExIndexOf(@Text, '\b' + @FirstSearchWord + '\b') + 1) set @Text = SUBSTRING(@Text, case @IndexOfDot when 0 then 0 else @TextLength - @IndexOfDot + 3 end, 300) while (select COUNT(*) from @TempSearchWords) > 0 begin select top 1 @CurrWord = Word from @TempSearchWords set @Text = dbo.RegExReplace(@Text, '\b' + @CurrWord + '\b', '<b>' + SUBSTRING(@Text, dbo.RegExIndexOf(@Text, '\b' + @CurrWord + '\b'), LEN(@CurrWord) + 1) + '</b>') delete from @TempSearchWords where Word = @CurrWord end insert into @FinalResults select * from @PrelimResults where [UID] = @CurrID delete from @PrelimResults where [UID] = @CurrID end </code></pre> <p><strong>Several notes:</strong><br> 1. Nested while loops probably aren't the most efficient way of doing it, however nothing else comes to mind. If I were to use cursors, it would essentially be the same thing?<br> 2. <code>@FirstSearchWord</code> here to refers to the first instance in the text of one of the original search words, so essentially the text you are replacing is only going to be in the summary. Again, it's quite a basic method, some sort of text cluster finding algorithm would probably be handy.<br> 3. To get RegEx in the first place, you need CLR user-defined functions.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload