I recently worked with some data from an ERP which stored customer’s email addresses like so: firstname.lastname@example.orgemail@example.com.
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'
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>'
Just had this little snippet sent to me by a fellow SQL slinger. It uses an undocumented stored procedure to loop though your DBs and run an expression against each DB. Check out his example below which will return all user roles for each DB.
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
It’s so simple it would be to easy to forget.