Update SQL Keep Characters to the Left

Today I ran across an issue with an SQL table that received an injection that inserted bad data at the end of certain fields in all of the records.  I had to write some code that would find the bad data and strip it off of the end of the good data.  After writing the code, I thought I would share with those interested.

Example:

In this specific example, the tblNews table received a SQL Injection that inserted a long strand of bad data at the end of the Body field.  Lucky for me, the Injections all started with '<div style='.  That specific hunk of characters is what I used to track them all down.  I first created a query to pull all the records to see what I was looking at.  Then typed an Update statement that removed all the characters to right of  '<div style='.

Initial Pull:

SELECT * from tblNews
where Body like '%<div style=%'

Update Code:

UPDATE tblNews
Set Body = LEFT (Body, CHARINDEX('<Div style=', Body) - 1)
where Body like '%<div style=%'

If you like this post and want to see more, follow me on my website www.chadcompton.com Or if you prefer...

Concatenate 2 Integers Into 1 Varchar (SQL)

Yesterdays Just A Tip Felt so good, I thought I would do it again today.  Twice in one week.  You guys & gals get a great tip, while I get another notch under my belt.  Win.  Win.

Today's coding issue involves 2 Integer fields that need to be concatenated into 1 big field and that field needs to be Varchar.  To accomplish this, you will need to Cast each  Integer as a Varchar then concatenate them.

For Example, in an SQL table called tblPetOwners you have 2 fields (OwnerNumber & PetNumber)   which are Integers and wish to join them with an '&' in between them.  To do this, you have to CAST them both as Varchar before concatenating them.  Below is the best way I have came across to accomplish this task and I show you the data in the table, the code itself, followed by the results.

Table tblPetOwners Data:

OwnerNumber
PetNumber
223122
543224
643233
235432
332342
654523

Example Code:

SELECT CAST(OwnerNumber AS VARCHAR(10) ) 
+ ' & ' + 
CAST(PetNumber AS VARCHAR(10) ) OwnerAndPet, OwnerNumber, PetNumber 
FROM tblPetOwners

Results Data:

OwnerAndPet
OwnerNumber
PetNumber
223122 & 543224
223122
543224
643233 & 235432
643233
235432
332342 & 654523
332342
654523

If you like this post and want to see more, follow me on my website www.chadcompton.com

Or if you prefer...

Union & Count Together in A SQL Query

I know it has been a while since I have given you a good tip...So without further ado.

Today I had a situation where I needed to give a count of items that were across a couple of tables and involved a few unions in SQL.  Since my query took up about 2 pages, I figured I would make a simple version for all you folks out there reading this.  I hope it helps.

For Example, let us say you have two tables ( Results & ArchivedResults) and were needing to get a count on them both.  Let us also assume you didn't use a JOIN because of the different criteria in each SQL statement.  Based off those results you need to get a count.  Below is how this can be accomplished.

Example Code:

SELECT PullResults.Name, COUNT(*) 
FROM  (SELECT Name FROM Results
UNION ALL
SELECT Name FROM ArchivedResults) as PullResults
GROUP BY Name
ORDER BY Name

If you like this post and want to see more, follow me on my website www.chadcompton.com

Or if you prefer...

SUM Using Group By (SQL)

At some point, you will be required to use the SQL GROUP BY clause with the SQL SUM function.  My time to use this was today and I thought I would create this post to help anyone else that may be needing to accomplish the same.

For example, you could also use the SQL SUM function to return the name of the department and the total sales (in the associated department).

Example Code:

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department;

Because you have listed one column in your SQL SELECT statement that is not encapsulated in the SQL SUM function, you must use the SQL GROUP BY clause. The department field must, therefore, be listed in the SQL GROUP BY section.

If you like this post and want to see more, follow me on my website www.chadcompton.com

Or if you prefer...

AVG Function (SQL)

Over the last couple of coding posts I have showed you how to use the SUM function.  This being said, I thought I would show you how to use the AVE function in SQL.  


Use the AVG() Function to get the average of a of a specific numeric column.  Below is an example of it being used.

SQL AVG Example:

SELECT  AVG(ColumnName) 
FROM TableName;

If you like this post and want to see more, follow me on my website www.chadcompton.com

Or if you prefer...

Markers Mark Tour

Please Note this tour was done with YELP of Lexington, therefore your experience will not be exactly the same.


This was an Official Yelp Event (OYE) put on by Heather P our  Lexington's Great Yelp Community Ambassador.  I swear these events keep getting better and better.

This specific event started out with a meet up in Isle 35 at Keenland where we were greeted with Bloody Marks (aka bloody mary's made with good old Makers Mark) and North Limestone Doughnuts provided by Jacklyn E., our lovely tour guide from Makers Mark.  This was truly a perfect tailgate breakfast before we hopped on the Mint Julep Tour bus and were on our way to Loretto, the home of Maker's Mark Distillery.  

The trip itself was mostly through the beautiful country side down small winding roads most of which is part of the Kentucky Bourbon Trail.  Mint Julep Tours provided us with a great driver and the perfect tour guide leader (Bob and John).  They pointed out great tid bits and facts  about the local area as we drove through the small towns.  They also pointed out great destination places along the way, like the man with 500+ riding lawn mowers outside his house.  I have to admit as a geeky road trip kid, that was a great stop. lol. We also learned about bourbon on the way with the help of Jacklyn.  Great facts before we even got to Maker's Mark.

Once we arrived at Maker's Mark Distillery, we grabbed some tasty Maker's Mark Bourbon Coffee and started our Distillery tour.  This is where Jacklyn shined.  She was so knowledgeable of Maker's Mark history and the bourbon making process, it was amazing.  Being on a special tour, we got to go into a couple buildings that most tours will not allow.  The actually Distilling Building was one and the Quality Control Area was the other.  

In the Distilling Building, we got to see the mill that does all the corn and grain grinding for every batch of bourbon that gets made.  We got to see and taste the 1 day mash that was bubbly and sweet (Odd I know).  Then sampled one that was more bitter and corny (BLAH), video proof below.   We saw the White dogs which is the liquid at 120 and 130 proof which we also got to sample.   Be aware this isn't on the normal tours. 
Next room was the labeling building.  Every label on every bottle is printed and then cut on an old repurposed printing press.  This cutter only cuts a max of 4 sheets at a time.  A time consuming process but well worth the effort.

The super secret Quality Control Building was next.  These ladies in here are like CSI of the booze world.  Very impressive.  They do samplings of all the batches at many stages of the process.  They also keep one bottle of every batch for referencing.  They have 25 workers that sniff, sip and sample bourbon every morning.  Those lucky people.  What a job.   This was also the place where Maker's 46 was invented.  
The storage barn was next.  There we got to see the inside of a barrel and learned how the rotate the casts manually by hand from upper levels of the barn to the lower level.  They don't use climate control facilities here. It takes average of 6 years or so to get the flavor profile that makes Maker's Mark so special.  There isn't an exact day per say, the bourbon will tell them when its ready by its flavor.  At that point is is diluted with their distilled water to get the proof they require.  Unless you are lucky enough to buy Straight Cast (like I did).  

The Bottling/Dipping building was next stop on our tour, a few of the videos are listed below.  There we saw the empty bottles move from cleaning to filling and sealing.  Then hand dipped in the signature red wax after the label has been slapped on.  It was a fancier version of the beer scene from Lavern and Shirley opening scene.  It was really impressive.  

Then we arrived at the tasting room.  Here we got to sample 5 different bourbons (2 which aren't normally on the tastings).  Under Mature, Fully Mature, Over Mature, Maker's 46, Cast Straight.   Jacklyn rocked out here as well.  She explained to us how we should sniff the bourbons before sipping.  Basically how to experience bourbon with all our senses.  I loved it.  Each bourbon tasted like bourbon, but had different hints of flavors and smells.  Pretty neat. They gave us Maker's Mark distilled water also.  WHO KNEW?  Freakin Water from their Super Secret Limestone Lake.  

Next we helped our selves to Bourbon Balls as we walked into the Beautiful hall filled with hand blown glass made by Stephen Rolfe Powell.  It is so beautiful.  Hundreds of unique, brightly colored glass lines this ceiling in this hall way.  So stunning.  There are also hidden angels in here as well.  I forgot what their meaning was.

The gift shop was the last stop on our Maker's Mark Distillery Tour.  We were given a bottle of Maker's and got all gussied up and were able to dip our own bottles in the signature red wax.  Such a great experience.  Here we also shopped and bought other goodies.  I got Round Ice trays and Maker's 46 Straight Cast (which I also got to dip).

Then we hopped on the bus and headed home.   Lunch boxes provided by Joe's Deli. (no more room) Thanks ALL.  Keep a look out for my next article about touring Wild Turkey.

Video Snipits:


Random Pictures Below:

If you like this post and want to see more, follow me on my website www.chadcompton.com Or if you prefer...