ASP.NET Programmer

SQL Server Noise Words

If you do FULLTEXT searches in SQL Server, you are very aware of the limitations of noise words. Noise words are basically words that get dropped from the query. They are like common articles like ‘a’, ‘an’, etc. These words are contained in the file c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseENU.txt (path may vary based on your directory setup). Here is the full list if you are looking to see if one of your terms is getting dropped

SQL OUTER JOIN: The When, the Where & the How To

So I’m trucking along wrapping up a project, throwing in a few last minute testing changes and I put in a new textbox that isn’t like any other others. All the previous were connected up via the database with a store item. This button is more of an aggregator for checkboxes or radiobuttons. So the essential problem was, it wasn’t pulling back in my stored procedure since it didn’t have that store item associate with it. No problem. We’ll just employ our OUTER JOINs. As you no doubt know, JOINs or INNER JOINs connect up two database tables and only return the results that exists in both databases. OUTER JOINS aren’t quite as picky. It will bring back the results regardless whether its contained in both tables or not. It is going to have the following syntax:

INSERT, UPDATE & DELETE : Basic Sql Syntax

I’m always experiencing a brain freeze when it comes to the simple stuff. Case in point, SQL Statements. Outside of the SELECT statement, the format of the update, insert, delete never seem to fall into place like I want them to. So here is a few examples to always keep me straight:

>> UPDATE Statement UPDATE Persons SET Address=’123 Main St’, City=’Portland’ WHERE LastName=’Programmer’ AND FirstName=’Joe’

>> INSERT Statement INSERT INTO Persons VALUES (4,’Programmer’, ‘Joe’, ’123 Main St’, ‘Portland’)

>> DELETE Statement DELETE FROM Persons WHERE LastName=’Programmer’ AND FirstName=’Joe’

Create Excerpt from Description Blurb

So the task at hand is taking a verbose piece of description text and breaking it down to the first 300 characters. Not only are we going to trim this to 300 characters, but we are going to drop off all the text that follows the last period. This will serve as teaser copy on our store page to get our fair reader to click over to read the full description if interested. So our first step will be taking that large block and cutting it down to 300 characters:

This SQL query basically says we want the left 300 characters of the description field, and we are going to store it in a variable we call excerpt. Now comes the tricky part of dealing with the text after the period. I’ve created a console application in Visual Studio to help whittle down the results. The following sets up our initial query:

Now we need to loop through that results set so we can pair down this data.

The program doesn’t need to worry about any descriptions that already end in a period or those ending as a url string so let’s skip them.

Here’s where we identify where the last period in our content block resides.

SQL IF…ELSE: CASE Statement

I was looking for a way to approach my SQL query that would work similar to a traditional if – then conditional in c#. So I came across SQL CASE statements, and they are exactly what I was looking for. I can do a test conditional based on one of the values passed back from the query to conditionally set a value. An example with multiple conditions of this follows:

Basically, we are saying that when the title of our book is equal to ‘ASP.NET Programming’ and there is no set price for the current year, use last year’s price. For everything else, just use this year’s price. We define this new custom variable as ‘Pricing’.

Reference Article: http://www.databasejournal.com/features/mssql/article.php/3288921/T-SQL-Programming-Part-5—Using-the-CASE-Function.htm

Looking for NULL Values in SQL

After spending much too long trying to figure out why a SQL CASE statement wasn’t correctly evaluating the second condition in this statement:

I realize rather foolishly it is the way I am comparing the value to NULL. SQL is always IS NULL and not = NULL.