Removing SQL Server Full Text Noise Words from a Search String

Remove SQL Full Text Noise Words

I'm not going to get into the specifics on constructing full text queries (at least not yet). In this article I'm going to tell you the full text noise words and provide a function for stripping them from queries so you don't get an error.

Have you ever gotten this error when executing a search against your full text index:

Microsoft OLE DB Provider for SQL Server error '80040e14' 

Execution of a full-text operation failed. A clause of the query contained only ignored words.

SQL Server Full Text Indexing provides support for sophisticated word and phrase searches. The full text index stores information about words and their location within a given column, This information is used to quickly complete full text queries that search for rows with particular words or combinations of words.

The noise words (assuming you did a standard install of SQL Server 2000) are located at:
\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config
In that directory, you'll see a bunch of files named noise with different extensions. The extension indicates the language. The English noise files is noise.eng. If you open that file with a text editor, you can see all the noise words (and symbols, like the $ for example). A search containing any of these words or symbols will cause an error.

There are a couple of options here for preventing that error. One is to remove the noise word from the noise file, then rebuild your full text index. I did this for a site where I aggregated video games for price comparison. I needed visitors to be able to search on Playstation 2. Well, the number 2 is in the noise file, so I took it out.

Even if you decide to take some words out of the noise file, you'll still need to prep your search string by removing any noise words that are left. The easiest way to do that is a function. When someone does a search, pass their search phrase into the function and then return the phrase with the noise words stripped out. There are several ways to do this, but here's the method I went with:

Function PrepSearchString(sOriginalQuery)
 strNoiseWords = "1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 0 | $ | ! | @ | # | $ | % | ^ | & | * | ( | ) | - | _ | + | 
= | [ | ] | { | } | about | after | all | also | an | and
 | another | any | are | as | at | be | because | been | before | being | between |
 both | but | by | came | can | come | could | did | do | does | each | else | for |
 from | get | got | has | had | he | have | her | here | him | himself | his | how |
 if | in | into | is | it | its | just | like | make | many | me | might | more |
 most | much | must | my | never | now | of | on | only | or | other | our | out |
 over | re | said | same | see | should | since | so | some | still | such | take |
 than | that | the | their | them | then | there | these | they | this | those |
 through | to | too | under | up | use | very | want | was | way | we | well | were |
 what | when | where | which | while | who | will | with | would | you | your | a |
 b | c | d | e | f | g | h | i | j | k | l | m | n | o | p | q | r | s | t | u | v |
 w | x | y | z"

 arrNoiseWord = Split(strNoiseWords,"|")

 For i = 0 to UBound(arrNoiseWord)
  sOriginalQuery = " "&LCase(sOriginalQuery)&" "
  sOriginalQuery = Replace(sOriginalQuery," "&Trim(arrNoiseWord(i))&" "," ")
 Next
 PrepSearchString = Trim(sOriginalQuery)
End Function 

How it works:

I took all the noise words and put them in one long pipe delimited string

Split the string to make an array

Loop through the array looking for noise words

When I find one, replace it with a space

Some other considerations:

In the function above, I added an extra space before and after the pipe. This was to allow the long string to break and fit onto the page. To account for that, I use the Trim command referring to an item in the array. If you build the string without the spaces, you won't need the Trim (though it won't hurt anything).

I took the original query and put a space on either end. This is to account for a noise word being at either the beginning or the end of the search phrase.

I also use LCase to put the original query into all lower case. I could do a vbTextCompare, but it was easier for just make the term all lower case.

Lastly, I trim the original query when assigning it to go back to account for a space being at either end (when the noise word started or ended the query)

Once you have the noise word free search string, you can build your query. Be aware: if the search query contains noise words only, this returns an empty string. You should for that in your code.

Geek Babe Monday

Sometimes programming can be so.... boring, so I thought I'd liven up RetroWebDev with a new feature: Geek Babe Monday.

Every Monday, I'll post a Hot Geek Babe. Don't be shy if you have suggestions for future features!
Kristanna Loken from Terminator 3, the ScFi Movie Dark Kingdom, BloodRayne, and the upcoming In the Name of the King: A Dungeon Seige Tale (due out in 2007).

ASP Function to Capitalize the First Letter of All Words in a Sentence

Here's a function you can use that will capitalize the first letter of all words in a sentence. Simply pass in the text string containing the words you want capitalized.

You send in: "The quick brown fox"
You get out: "The Quick Brown Fox"

First call the function:

YourString="The quick brown fox"
strCaps = Capitalize(YourString)

The Function:

Function Capitalize(X)
 'return a string with the first letter of the word capitalised
 If IsNull(X) Then
  Exit Function
 Else
  lowercaseSTR = CStr(LCase(X))
  OldC = " "
  MyArray = Split(lowercaseSTR," ")
  For IntI = LBound(MyArray) To UBound(MyArray)
   For I = 1 To Len(MyArray(IntI))
    If Len(MyArray(IntI)) = 1 Then
     newString = newString & UCase(MyArray(IntI)) & " "
    ElseIf I=1 Then
     newString = newString & UCase(Mid(MyArray(IntI), I, 1))
    ElseIf I = Len(MyArray(IntI)) Then
     newString = newString & Mid(MyArray(IntI), I, 1) & " "
    Else
     newString = newString & Mid(MyArray(IntI), I, 1)
    End If
   Next
  Next 'IntI
  Capitalize = Trim(newString)
 End If
End Function

Archive Calendar for Blogger Blogs

After I'd gotten numerous posts in my RetroWebDev blog, I noticed that it can be difficult to locate a post after clicking on one of the monthly links under the Archive section.

It didn't take much research to find a handly script that generates an Archive Calendar for blogger. To see it in action, click on one of the monthly links uder the Archive section on the left.

With the Archive Calendar, you can click right a specific day to see the post(s) on that day. I like.

Mucho thanks to whomever created this script.

ASP Includes Explained

Include files are external files that you can easily add to any page:

It's important to note that include files are processed and inserted BEFORE scripts on the page are run. Included files can contain functions, sub-routines, navigational elements, or just about anything else. Include files are at the heart of making efficient ASP based sites, as they allow you to put you code into easily re-usable chunks.

There are two forms of the include statement: Virtual and File:

By using Virtual, you can include any file no matter what its location because Virtual starts as the root directory of the site. In contrast, the File statement starts in the CURRENT directory (the same directory as the file containing the Include).

Example #1

could include a file from the AAA directory, even if the page with this statement is in the /BBB/ folder.

Example #2:

would fail when used in any file not located in the AAA directory.

Example #3:

will succeed from any directory on the same level as the AAA directory. It's instructing the include to go up one directory and then into the AAA directory to get the file. However, if the page that contains the include is moved to a different level in the directory structure, the Include statement would fail.

I almost always use the Virtual format. That way I know I'm starting at the site root. Even if I move the page containing the include to a different directory, it will still work.

SQL to get Records for Last 24 Hours using DateAdd

One of the applications I wrote is a logging system. One of the requested modifications is that I make it easy for managers to look at entries made in the last 24 hours. Right now, I have hard-coded links for Current Day, Yesterday, Last 7, etc.

Current Day shows entries made since midnight. Yesterday shows entries made for the previous day. So right now there's no quick way to get the entries for the last 24 hours.

The solution was simple:

Select * from Entries where DateAdded >= " & DateAdd("d",-1, Now())

How it works:

The VBScript Now() function gets the current date and time. I then use the DateAdd funciton to subtract 1 day. I then get all entries with a DateAdded timestamp great than or equal to resulting date and time.

Alternately, you could subtract hours:

Select * from Entries where DateAdded >= " & DateAdd("h",-24, Now())

DateAdd is pretty nifty.

Syntax:
DateAdd(datepart, number, date)

Datepart can be (abbreviation):
year (yyyy)
quarter (q)
month (m)
day (d)
week (ww)
hour (h)
minute (n)
second (s)

Examples:
1 month from today: DateAdd("m", 1, Now())
100 years ago: DateAdd("yyyy", -100, Now())
10 minutes from now: DateAdd("m", 10, Now())
1 quarter (3 months) ago: DateAdd("q", -1, Now())

WWW Versus Non-WWW URLs: Dupe Content and Redirecting

I'm not real clear on the technical reasons behind it all, but the consensus in the SEO community is that it's detrimental because of duplicate content penalities to have your web site operating on both the www and non-www addresses. Ideally, you should pick one address or the other and have visitors (human and otherwise) who come to the other forwarded using a permanent redirect. So if you choose www.YourSite.com as your web web address, you should have all visitors to Your.Site.com forwarded to www.YourSite.com.

This is easy enough to do for one page, but how about some handy code that will automatically forward all your pages using a search engine friendly 301 redirect?

This code that will forward any visitor to the www version of your site, no matter what the entry page. Place this code at the top of every page:

<%
Dim strDomain, strURL, strQueryString, strHTTPPath,vTempNum

'Get page domain 
strDomain = LCase(request.ServerVariables("HTTP_HOST"))

'Check for www
If Left(strDomain, 3) <> "www" Then
 strHTTPPath = Request.ServerVariables("PATH_INFO")

 'If page is default.asp, send to root 
 If right(strHTTPPath, 12) = "/default.asp" Then
  vTempNum = Len(strHTTPPath)-11
  strHTTPPath = Left(strHTTPPath,vTempNum)
 End If

 'If page is index.asp, send to root 
 If right(strHTTPPath, 10) = "/index.asp" Then
  vTempNum = Len(strHTTPPath)-9
  strHTTPPath = Left(strHTTPPath,vTempNum)
 End If

 'Set the new URL
 strQueryString = Request.ServerVariables("QUERY_STRING")
 strURL   = "http://www." & strDomain & strHTTPPath

 'If any, pass on query string variables
 If len(strQueryString) > 0 Then strURL = strURL & "?" & strQueryString

 '301 Redirect to www version
 Response.Status = "301 Moved Permanently"
 Response.AddHeader "Location", strURL
End If
%>

Likewise, here is code that will redirect to the non-www version:

<%
Dim strDomain, strURL, strQueryString, strHTTPPath,vTempNum

'Get Page domain
strDomain = lcase(request.ServerVariables("HTTP_HOST"))

'Check for www
If Left(strDomain, 3) = "www" Then
 'Change to non-www version
 vTempNum = Len(strDomain)-4
 strDomain = Right(strDomain,vTempNum)
 strHTTPPath = Request.ServerVariables("PATH_INFO")

 'If page is default.asp, send to root 
 If right(strHTTPPath, 12) = "/default.asp" Then
  vTempNum = Len(strHTTPPath)-11
  strHTTPPath = Left(strHTTPPath,vTempNum)
 End If

 'If page is index.asp, send to root 
 If right(strHTTPPath, 10) = "/index.asp" Then
  vTempNum = Len(strHTTPPath)-9
  strHTTPPath = Left(strHTTPPath,vTempNum)
 End If

 'Set new URL
 strQueryString = Request.ServerVariables("QUERY_STRING")
 strURL = "http://" & strDomain & strHTTPPath

 'If any, pass on query string variables
 if len(strQueryString) > 0 Then strURL = strURL & "?" & strQueryString

 '301 redirect to non-www version
 Response.Status = "301 Moved Permanently"
 Response.AddHeader "Location", strURL
End If
%>

Scraping Search Engines for Page Content

It's no secret that optimized page content can help you rank better in organic search engine results. While page content is not the deciding factor, it's stil important, especially for long tail search terms. No matter what your page content, you're probably not going to be able to rank well for the term 'dvd player' (or any other exceptionally competetive term). But you might be able to rank on a long tail term such as 'panasonic portable dvd player model 123ABCxyz'. For such a long tail phrase, optimized page content can help.

But how to get the content? Well, you could write it. Or buy it. Or... scrape it from somewhere else.

If you chose the scraping solution, I highly recommend that you have some original content on the page. Write a capsule review, or your own description, or anything else that likely doesn't exist on some other site (until, of course, someone scrapes it).

The following process will step you through scraping content from a search engine and adding it to your page.

WARNING: Implementing these techniques could very well get you banned from the search engines!

1. Decide on the keyword you want to generate content for. For this example, I'll use the fictitious Panasonic DVD model from above.

2. Decide how many search engines you're going to scrape content from. For this example, I'll use 3: MSN, Yahoo, and Gigablast.

3. Since I'm using 3 search engines, I generate a random number between 1 and 3.

4. Now pass the number into a page scraping function to go out and get the search results for that phrase from the search engine. The way you might do it is pass the number and keyword into the function:

strContent = ScrapeContent(intRandNum,"panasonic portable dvd player model 123ABCxyz')

Then, in the function, have a Case statement to assign the url based on the random number:

Function ScrapeContent(TheEngine, TheTerm)
 Select Case TheEngine
  Case 1
   URL = "http://search.msn.com/results.aspx?q="&TheTerm&""
  Case 2
   URL = "http://search.yahoo.com/search?p="&TheTerm"&"
  Case 3
   URL = "http://www.gigablast.com/search?q="&TheTerm&""
 End Select

 Set xmlObj = Server.CreateObject("MSXML2.ServerXMLHTTP")

 xmlObj.Open "GET", url, true  
 Call xmlObj.Send()

 On Error Resume Next

 If xmlObj.readyState <> 4 Then xml.waitForResponse 3

 If Err.Number <> 0 Then 
  ScrapeContent = "There was an error retreiving the remote page"
 Else
  If (xmlObj.readyState <> 4) Or (xml.Status <> 200) Then
   xmlObj.Abort
   ScrapeContent = "Problem communicating with remote server..."
  Else
   ScrapeContent = xmlObj.ResponseText
  End If
 End If
End Function

5. In the above example. we know have the remote content assigned to a variable named strContent. Parse that string to strip out eveything between the body tags:

strContent = Mid(strContent,Instr(strContent,"<body>")+6,Instr(strContent,"</body>"))

6. Now that you have the body content, replace all the breaks with a space; this is because words might run to gether otherwise:

strContent = Replace(strContent,"<br>"," ")

7. There are some extraneous words that have no relevancy to your search term that you're probably going to want to strip out as well. For example, Yahoo has additional links that follow the listing: 'Cached', 'More from this site', 'Save' etc. I recommend customizing a function to strip out all the non keyword related carp that clutters the search results you just scraped.

8. Now you have fairly clean copy relevant to your keyword, the last thing to do strip out the HTML.

The end result: a big chunk of text directly relevant to your keyword.

There's a few things you can do with it: plop it right on the page (not recommended), hide it in a div, or - my recommendation - hide it in a div and reverse cloak it, so that only search engines can see it. Hopefully your pages will start climbing higher in the organic results.

Leverage those favorable results quickly! Sooner or later you'll find your site banned, either because the spiders got smarter or a competitor reported you.

Good luck!

CSS Overflow Property

I'm working on a site where visitors will be able to upload their own images, which I will then be displaying. In order to keep page formating, I'll be automatically resizing the images to 125 pixels in height. The image itself will be placed in a 125x125 div. But what if it's wider than 125? I wanted it to be cropped so as not to mess up the page format. Enter the overflow property

<div style="height:125px;width:125px;overflow:hidden;">

Overflow has other parameters as well:

overflow: auto - This will insert a scrollbar - horizontal, vertical or both only if the content in the block requires it. For practical purposes, this is probably the most useful for creating a scrolling area.

overflow: scroll - This will will insert horizontal and vertical scrollbars. They will become active only if the content requires it.

overflow: visible - This will cause the block to expand to view the content.

overflow: hidden - This forces the block to only show content that fits in the block. Other content will be clipped and not hidden with no scrollbars.

How to Strip out HTML with an ASP Reg Exp Function

Ever wanted an easy way to strip out all the HTML from a string? There are a variety of reasons you might want to do this. Maybe you offer a mail page feature and want to strip out the HTML to send a text email. Or maybe you're a Black Hat SEO Sith Master and are scraping content from millions of pages to build an AdSense empire!

In any case, here's an easy way, using regular expressions, to strip out all the HTML from a string.

This function accepts a string input (the string whose HTML tags are to be stripped). The regular expression pattern <(.|\n)+?> is used to get all matches of < and > characters with at least one character in-between. The Replace method of the regular expression object is then used to replace all instances with an empty string (""). Finally, all remaining < and > signs are replaced with their respective HTML encoded forms.

Something to consider: if you strip out the <BR> tags and you're re-displaying the string, it will all run together. The fix there would be to do a straight replace BEFORE sending the string to the stripHTML Function:

<%
TheString = Replace(TheString,"<BR>","{}",1)
%>

This replaces all the Break tags (those in both upper and lowercase) with {} right next to each other. Then send TheString to the StripHTML Function:

<%
Function stripHTML(strHTML)
 Dim objRegExp, strOutput
 Set objRegExp = New Regexp

 objRegExp.IgnoreCase = True
 objRegExp.Global = True
 objRegExp.Pattern = "<(.|\n)+?>"

 'Replace all HTML tag matches with the empty string
 strOutput = objRegExp.Replace(strHTML, "")

 strOutput = Replace(strOutput, "<", "&lt;")
 strOutput = Replace(strOutput, ">", "&gt;")

 stripHTML = strOutput    'Return the value of strOutput

 Set objRegExp = Nothing
End Function
%>

Now you have a string with all the HTML stripped out and all the Break tags replace with {}. Now do one last Replace to put the Break tags back in:

<%
TheString = Replace(TheString,"{}","<BR>",1)
%>

That's it!