The most time-saving sql code I have ever written - Digital Tool Factory blog The most time-saving sql code I have ever written - Digital Tool Factory blog

The most time-saving sql code I have ever written

database schemaphoto © 2007 gnizr | more info (via: Wylio) I decided to update my JargonDatabase.com web property last month started to update define the user-submitted jargon terms.  Much to my surprise users had submitted over 15,000 unique undefined jargon terms.  Some of them were conceptual duplicates, like “whites of their eyes” and “the whites of their eyes”, but how to filter the terms to see if they were duplicates or not?

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

 

One response to “The most time-saving sql code I have ever written”

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *






Copyright 2011 Digital Tool Factory. All Rights Reserved. Powered by raw technical talent. And in this case, WordPress.