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…

Cascade Dropping Tables in PostgreSQL

Dropping tables with dependent objects is usually a tricky task. If the database is normalized, usually foreign keys will prevent the drop from occurring. Other objects such as views can also prevent dropping the object. It’s a very good thing these drops fail. We don’t want to accidentally blow away those necessary objects. However, if…

Instantly Stage Data Using PowerShell

Why? There are so many times I’ll receive a random CSV and be asked to compare something to a table in a database. Sometimes it’s just easier to work with these results in SQL Server. It’s pretty straight forward to perform this task using the “Import Flat File” option in SQL Server Management Studio (SSMS)….

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….

To PowerShell, or Not To PowerShell

I’m opening up a can of worms with this topic. PowerShell usage is a heavily debated topic in the SQL Server community. This is similar to the classic “Tabs or Spaces” as an indentation style debate, there’s no clear winner. Both sides love to argue their preference. We all know spaces win. Or do tabs…

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…