After much pondering I decided to try to match words in the term with words already in the database – there would be many false positives, but was that method was the best way of ensuring I did not delete anything by accident.
To do that I created a function and a stored procedure. The function:
CREATE FUNCTION Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx – 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) – @idx)
if len(@String) = 0 break
end
return
end
The function takes a string, splits it at every occurrence of a space (” “), inserts those new records into a table, and then returns that table. From there I created the following procedure.
CREATE PROCEDURE FindSimilarSearchTerms
@SearchTermID int
AS
BEGIN
declare @SearchTerm nvarchar(450)
SET @SearchTerm=(SELECT top 1 SearchTermTerm FROM UserDefinedTerms WHERE SearchTermID=@SearchTermID)
CREATE TABLE #tmp (TermID int IDENTITY, TermWord nvarchar(450))
INSERT INTO #tmp(TermWord) select items from dbo.split(@SearchTerm, ‘ ‘)
SELECT JargonID, JargonTerm FROM JargonApprovedTerm, #tmp WHERE JargonTerm LIKE ‘%’+TermWord+’%’
DELETE FROM #tmp
DROP TABLE #tmp
END
GO
The procedure takes the ID of a search term, grabs the text of the user-submitted name (the “SearchTermTerm” field) and from there creates a temporary table, and then matches any word in the user-submitted name to any word in the approved jargon table in the database, that provides a quick way of knowing whether or not I can safely delete a term. Definitely a time consuming task, but a lot better than it could be.
This post originally appeared on the Stronico blog – with the absorption of Stronico into Digital Tool Factory this post has been moved to the Digital Tool Factory blog
|
|
Written By Steve French |
I could not find a way to reply to you through the JARGON DATABASE, but this reply pertains to that Web site. Your definition of SPEAK TRUTH TO POWER needs to be updated. Anita Hill USED the phrase, she did NOT develop it. The first documented use of the phrase was in a 1955 pamphlet published by the Quakers regarding totalitarian political regimes. The man who came up with the phrase thought he had read it in earlier (17th century) Quaker writings, but no one was ever able to find any earlier documented use of the phrase. A simple Internet search will confirm this.