About
Digital Colony is the technical web site for Michael Allen Smith of Seattle.
Category Archives: SQL
Writing Your Own Search Engine Using SQL Server
My coffee site INeedCoffee needed a better search engine. I had thrown some basic SQL together when the site was launched back in 1999. It did an OK job when the site didn’t have much content. Over the years, the … Continue reading
Replacing The Extended ASCII Dash in C# and SQL
Not all dashes are created equal. That’s what I learned today. If you look at the ASCII Character Codes CheatSheet you will see 3 different dashes. The first is the normal one. The other two are considered extended ASCII. Extended … Continue reading
Convert Hexadecimal to Integer in SQL
Below is a handy function for converting a hexadecimal value into an integer using a SQL Server user defined function. IF OBJECT_ID(‘dbo.udfHex2Int’) IS NOT NULL DROP FUNCTION dbo.udfHex2Int GO CREATE FUNCTION dbo.udfHex2Int ( @hexstr AS varchar(1000) ) — Function converts … Continue reading
Date Scrubbing Function for SQL Server
When creating a report often the date column provides more detail than we need to see. Here is a handy little UDF that rounds a DATETIME column to either the MINUTE, HOUR, DAY, WEEK or MONTH. DateFirst UDF IF OBJECT_ID(‘dbo.udfDateFirst’) … Continue reading
Test Using EXISTS Before Inserting
Here is a sample stored procedure that prevents duplicate inserts with the same value by first performing a test using SQL Server’s EXISTS statement. IF OBJECT_ID(‘prcTagNameInsert’) IS NOT NULL DROP PROCEDURE prcTagNameInsert GO CREATE PROCEDURE prcTagNameInsert @tagName VARCHAR(50) AS IF … Continue reading
