I've never done it before but the first idea that comes to mind is as follows:

create table tHyperlinkedTerms (hyperLinkedTermsId int IDENTITY, keyword varchar(100), description varchar(4000))

create table tGlossary (glossaryId int IDENTITY, keyword varchar(100), description varchar(4000))

-- a n to m match on this table where n<=m or m<=n

-- with foreign keys pointing back to the appropriate Id columns

create table tGlossaryHyperLinkMatches (glossaryId int, hyperLinkTermsId int)

You could return two recordsets out of these tables... one with the glossary paragraph and the other with a list of words that need to be hyperlinked.

If you are outputting this to a web page, I think that you could do the hyperlinking/substitution on the clientside.

Other things to consider:

Terms that may appear in the hyperlinked list that in turn should be hyperlined.

IDEA #2

Benefit of idea 2 is that the hyperlinked area is separated.

Any word that you want to add a hyperlinked definition for can just be added to the hyperlink table without having to update the mapping table.

Find each individual word in a varchar string by finding the spaces in the string:

--will find spaces in the first 500 positions of the string

create table #spaces (id int IDENTITY, positionIndex int)

insert into #spaces

select ones+tens+hundreds as theCount from tParagraph

join (select 0 as ones UNION select 1 UNION select 2 UNION select 3 UNION select 4 UNION select 5 UNION select 6 UNION select 7 UNION select 8 UNION select 9) as ONES on 1=1

join (select 0 as tens UNION select 10 UNION select 20 UNION select 30 UNION select 40 UNION select 50 UNION select 60 UNION select 70 UNION select 80 UNION select 90) as TENS on 1=1

join (select 0 as hundreds UNION select 100 UNION select 200 UNION select 300 UNION select 400 UNION select 500) as HUNDREDS on 1=1

where id =@paragraphID and ones+tens > 0 and substring(text, ones+tens, 1) = ' '

order by ones+tens

--will give the words inbetween the spaces that you found:

--you'll need to make a fix to have it include the first word...

create table #Words (id int IDENTITY, words varchar(4000)

insert into #Words

select substring(text,a.positionIndex+1, b.positionIndex-a.positionIndex) from tParagraph c

join #spaces a on 1=1

left outer join #spaces b on a.id = b.id - 1

where c.ID = @paragraphID

Then you can compare this list of words with your hyperlinked words...

Obviously there's a lot to work out still. Finding commas and periods and what do you do when you even find the hyperlinked words???

Just some thoughts.

Good luck.

-jraha