Two Ways to Clean Email from ERP Database

I recently worked with some data from an ERP which stored customer’s email addresses like so:
Goofy right. Anyway, here are a couple of methods of cleaning up the data and returning only the first part of such a string.

STUFF(b.Email, PATINDEX('%#%', b.Email), ( LEN(b.Email) - PATINDEX('%#%', b.Email) + 1 ), '') 'Email2'
SUBSTRING( b.Email, 1, PATINDEX('%#%', b.Email) - 1 ) 'Email3'

Cleaning SQL Columns for Export

Several times a year I’m asked to run some queries and export the results to Excel, CSV or Tab delimited files.

If you’ve ever done this you may have run into bad exports due to line ending or tab characters in the DB columns. I’m always forgetting how to clean these up so here’s a reminder to me.

REPLACE( REPLACE( REPLACE( CAST( <column> AS VARCHAR(MAX) ), CHAR(13), ' ' ), CHAR(10), ' ' ), CHAR(9), '' ) AS '<column>'

Manual Install / Upgrade OpenSSH on Ubuntu

Recently I updated OpenSSH due to a PCI scan failure. We had OpenSSH 6.6 running on an Ubuntu 14.4.3 server. As of this writing OpenSSH is at 7.0 but has not been packaged for Ubuntu Aptitude yet.

UPDATE 08/23/2016: I just ran through this process again to install OpenSSH 7.3p on an Ubuntu 14.04 LTS box. Same commands worked without issue. One thing I did do different this time was uninstall the OpenSSH included with Ubuntu by running apt-get remove openssh-server. This guaranteed there was no conflict when installing from source.

This means I need to do a manual install of OpenSSH portable. It was pretty simple for me since I could use the default configuration. Your mileage may vary.

$ apt-get remove openssh-server
$ wget http://.../OpenSSH/portable/openssh-7.0p1.tar.gz
$ tar -zxvf openssh-7.3p1.tar.gz
$ cd openssh-7.3p1
$ ./configure
$ make
$ make install

To test this you can try connecting to local host to see the ssh version returned.
$ ssh -v localhost


How to Manually Fork a GitHub Repo

Github doesn’t allow you to fork one of your own repositories to your account. In my case the reason this issue raised it’s ugly head is I’ve a private repo that I need to fork. I can’t just fork it to another user account because then I’d have to pay for a second account to support private repos.

So here’s my workaround.

  1. Create a new empty repo on Github.
  2. Clone the new empty repo down to my local dev machine.
  3. Add the original repo as the upstream of the new local repo.
  4. Now just fetch and merge the upstream master to new repo master.

In this way I can pull and push code between the two repos. The downside with this is Github doesn’t see the second repo as a fork. So there will be no managing pull requests in the Github UI.

$ git clone
$ git remote add upstream
$ git fetch upstream master
$ git merge upstream/master master
$ git push