SimplyGold

This is, of course, ridiculously easy to do in almost any programming language on the market these days. But what if you want to do this in SQL Server? SQL Server doesn't provide a function for this, so we'll have to create our own. The most intuitive way to do it is to simply split the given phrase by whitespace and compare each word to the word that you want to search. But this is, of course, slow, especially as the phrase gets big.

I found a nice little function in the book Beginning ASP .NET 2.0 E-Commerce in C# 2005 by Christian Darie and Karli Watson. What they do is replace each instance of the word with a word that is one character longer. Then, the length of the new sentence - the length of the old sentence will give you the number of times the word appeared in the sentence.

An example:

• The word: "word"
• The sentence: "The word we're looking for is the word 'word.'"
• The replaced sentence: "The wordx we're looking for is the wordx 'wordx.'"
• Difference in lengths of the two sentences: 3, which is the number of times the word appears.

Pretty neat. ;-)

The reason to use the REPLACE function instead of doing it the normal way, of course, is because REPLACE is faster. The code, then, which I'm copying almost verbatim from page 174 of the book:

CREATE FUNCTION dbo.WordCount(

 @Word VARCHAR(20),
 @Phrase VARCHAR(1000))

 RETURNS SMALLINT

/* @BiggerWord is a string one character longer than @Word */
DECLARE @BiggerWord VARCHAR(21)
SELECT @BiggerWord = @Word + 'x'

/* Replace @Word with @BiggerWord in @Phrase */
DECLARE @BiggerPhrase VARCHAR(2000)
SELECT @BiggerPhrase = REPLACE (@Phrase, @Word, @BiggerWord)

/* The length difference between @BiggerPhrase & @Phrase is the number we're looking for */
RETURN LEN(@BiggerPhrase) - LEN(@Phrase)

The website's going to be down shortly as I upgrade my blogging software.

There are several perfectly legitimate reasons for why you might want to copy files from your iPod. It could be for backup. Or it could be that you want to be able to listen to music at work even if you forget your iPod at home. Unfortunately, iTunes doesn't let you copy the files from your iPod to your local computer (which is, in my opinion, perfectly ridiculous -- you don't know how many times I ranted at an otherwise good application for exactly this reason).

Enter YamiPod Yamipod is a free iPod manager for Mac OS X or Linux or Windows. It doesn't even require an installation (on Windows, at least); it runs straight out of the box.

ThemePorter contacted me recently regarding porting my templates over to Wordpress. OnePenny is now available. :-)

Storing binary data directly in the database is a fairly common practice. I recently found myself wondering how to read binary data from the database and write it to the browser.

For example, suppose you have a PDF file stored in the database. How do you send it to the browser? I ended up with this:

protected void Page_Load(object sender, EventArgs e)
{
 using (SqlConnection connection = new SqlConnection("Connection string goes here"))
 {
  connection.Open();

  using (SqlCommand command = connection.CreateCommand())
  {
   command.CommandText = "dbo.GetPdf";
   command.CommandType = CommandType.StoredProcedure;

   command.Parameters.AddWithValue("@Id", 1);

   using (SqlDataReader reader = command.ExecuteReader())
   {
    if (reader.Read() == false)
    {
     return;
    }

    int contentLength = Convert.ToInt32(reader.GetBytes(0, 0, null, 0, int.MaxValue));

    byte[] buffer = new byte[contentLength];

    reader.GetBytes(0, 0, buffer, 0, contentLength);

    Response.ContentType = "application/pdf";

    Response.BinaryWrite(buffer);
   }
  }
 }
}

This works, but I wonder if it's the most efficient way to solve the problem? For example, we're calling the GetBytes method twice, which means that we're actually reading the whole document just to get its length. If the document is huge, this is wasteful. In that situation, I'm guessing having a fixed-sized buffer and reading the data in a while loop would be the way to go.

2-column liquid width layout template. Made for 1280x1024 screens and modern browsers but it should degrade well for smaller screens and older browsers. The template is a result of my obsession with dark colors. Personally, I find dark-templates harder to create than the lighter counterparts, which is rather sad because I find it so much easier ( on my eyes ) to read light text on dark backgrounds than dark text on light backgrounds.

View the template online

Download the files

EXEC sys.sp_tables

But this will list every single table, including all the system tables, which we rarely ever want. To list only tables created by the user, add a @table_owner parameter. For most databases, this will be "dbo":

EXEC sys.sp_tables NULL, 'dbo', NULL, NULL, NULL

I thought long and hard about whether to make this a commercial template, because it involved a bit more work than the rest of them, but in the end decided that I'm going to keep it free. :-)

Enjoy!

View the template

Download the template files

For Pluto.

:-(