Clean(Translat)ing Phone Numbers

Dealing with phone numbers in a database can be a lot of fun. Especially when there are open form applications that allow for anything to be inserted. Many times, you might encounter a column filled with data that looks like this: PhoneNumber (614)-555-0171 6145550175 614-555-0181 (614)-555-0122 614-555-0168 (614)-555-0143 (404)5550171 4045550189 (404)-555-0125 (404)-555-1234 I had a…

Count String Character Occurences

Every now and then, I need to identify how many times a certain character appears in a string in SQL Server. Usually, I’ll do a few searches on Google and remind myself again that it’s not too difficult of a task. There’s not a built-in function to do this in SQL Server. Let’s use one…

#BESTWORSTCODE

Last month, on April 24th, Redgate hosted the #BESTWORSTCODE contest. Unfortunately, I wasn’t able to participate at the time. Better late than never, I suppose. Erik Darling blogged about his example recently. This gave me an idea of something really dumb I could do. PIVOT/UNPIVOT You might be able to take a guess at where…

STRING_SPLIT With Multi-Character Delimiter

Revisiting STRING_SPLIT. This won’t be the last time, I’m sure. I am so thankful that STRING_SPLIT is native to SQL Server as of SQL Server 2016. I still, however, feel that it has room for improvement. There are so many various ways to split strings, as Aaron Bertrand has excellently detailed in his blog post:…

Removing Duplicate Rows

Mistakes happen. Sometimes it may not even be a mistake. We’re left with the unfortunate requirement to remove exact duplicates from a table. We never would have been in this predicament if we were using a surrogate key or a primary key, but nevertheless, we’re here. Relational algebra suggests duplicate rows (or tuples) shouldn’t exist…

Inserting DEFAULT VALUES

Have you ever worked with a table that has default values accepted for each column? I hope not, but if so, this article is for you. I was recently working with a table like this for some testing. There wasn’t a “great” way to load thousands of rows into this table without using a loop….

Splitting a Column Into Columns

Every now and then, there may be some occasions where someone has stuffed some delimited strings into a single column. Here a few ways this could happen: Someone is avoiding normalization. Oops. There’s an “EmailTo” column where it’s possible to have multiple email addresses in one field. It’s easier to parse in SQL Server? That…

DROP [IF EXISTS]

What is it? Microsoft introduced a new method of dropping objects in SQL Server 2016. There are many times we want to conditionally drop objects to prevent errors or to allow scripts to be idempotent. Without first checking if the object exists, and it turns out it doesn’t exist, the DROP statement would fail. DROP…

BULK INSERT & CHECK Constraints

There are a few occasions where BULK INSERT seems to be a great solution for loading data from flat files. Sure, there are alternatives. You could also use BCP or SSIS, but BULK INSERT can come in handy in a time crunch. I don’t use this too often, but in the right circumstances, this could…

Using SPARSE Columns

Have you ever seen the SPARSE keyword used in a table definition? I came across this for the first time when looking at a holiday table that had a HolidayText column with a SPARSE property. So, what’s a SPARSE column? Sparse columns were introduced in SQL Server 2008. They’re intended to reduce the storage space…