Category Archives: Databases

My T-SQL Bookmarks

Posted on March 11, 2012 by

T-SQL Built-in Functions Creating a User-Defined Function  Unlike the output of a stored procedure, the result of a UDF can be accessed in a select statement While Loops

Posted in Databases | Leave a comment

Full-Text Search with SQL Server 2008

Posted on April 17, 2011 by

The full-text search capabilities of SQL Server 2008 make it possible to search text across multiple columns, weight search terms, and search by proximity. A full-text query can be much faster than the equivalent LIKE query. The first step is … Continue reading →

Posted in SQL, SQL Server | Leave a comment

Importing a Shapefile into PostgreSQL

Posted on March 18, 2011 by

I tried to import a shapefile to a PostgreSQL database using SPIT in QGIS, but kept getting an error because the gid column already existed in the .dbf. So I used the command line instead: shp2pgsql Downloads/Archive/data_dev.shp > data.sql then … Continue reading →

Posted in PostgreSQL | 1 Comment

Taking the top 5 of grouped ranked data in PostgreSQL

Posted on March 13, 2011 by

Working from a list of my ancestors’ occupations I wanted to list the top 5 occupations in each occupational category per decade, so that given year category description total … 1851 Agriculture Ag Lab 4 1851 Agriculture Farm Bailiff 1 … Continue reading →

Posted in PostgreSQL, SQL | Leave a comment

Creating a Rails 3 Project with PostgreSQL and TextMate on a Mac OS X Machine

Posted on January 30, 2011 by

I was advised to use RVM to manage Ruby on the Mac. Sign up as an Apple developer Download and install XCode Follow the instructions for installing RVM. Install RVM by executing $ bash < <( curl http://rvm.beginrescueend.com/releases/rvm-install-head ) in … Continue reading →

Posted in PostgreSQL, Ruby on Rails | Leave a comment

Checking if FullText Service is installed on SQL Server 2008

Posted on January 3, 2011 by

SELECT FullTextServiceProperty(‘IsFullTextInstalled’) This will return 1 if the service is installed.

Posted in SQL Server | Leave a comment

Concatenating column from many side of one-to-many into single record using STUFF

Posted on January 2, 2011 by

SELECT OneTable.col1, OneTable.col2, STUFF ( ( SELECT ‘ ‘ + mt.col1 FROM ManyTable mt WHERE mt.foreignkeycol = OneTable.primarykeycol FOR xml path(”) ), 1, 1, ” ) AS concatenatedcol FROM OneTable Note: remember the ” in the inner SELECT statement, otherwise … Continue reading →

Posted in SQL | Leave a comment