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>'

SQL, First and Last Days of Week

I needed to find the first and last days of the last week. Sunday being the first, Saturday being the last.

SELECT DATEADD(wk, DATEDIFF(wk, 6, '2013-08-11'), 6) AS START_OF_WEEK
SELECT DATEADD(wk, DATEDIFF(wk, 6, '2013-08-11'), 6 + 6) AS END_OF_WEEK