Thursday, July 8, 2010

How to Remove Some Thing from a Column’s Data


Here was a problem I was faced with today. A friend of mine was experiencing issues with two databases not syncing properly because one had a column (City) that contained a special character (a single quote). We needed to craft a query that would remove the single quote without changing any other portion of the column's data. There were two considerations that needed to be made:
  1. Ensuring the single quote was properly escaped so that it did not cause an error at run time.
  2. How to properly place the REPLACE function inside the UPDATE command.
It took me about 30 minutes to figure it out but here is what I came up with.
UPDATE [ARCUSTOMERS]
SET City = REPLACE(City, '''', '')
FROM [ARCUSTOMERS]
WHERE City LIKE '%''%'

Tuesday, July 6, 2010

Getting Back to Basics

In any art or profession one can never review the fundamentals too frequently. For this reason I am looking at doing some posts on the basics of relational theory. Not so much for the sake of my readers' (Hi, mom!) but more for my own sake. Here is what I currently have planned:

Fundamentals of Relational Theory and Database Design
Fundamentals of T-SQL Querying (2005 and 2008)
T-SQL Queries and DMVs

The third item is really one of my favorites, but I feel I have not put as much effort into it as I should.