SQL Blog of Zach Stagers

www.scratchbox.co.uk

Changing Data Types – Cast and Convert

October 31st, 2010 Posted in SQL
Oct, 31 2010
No Comments »

Changing data from one data type to another is commonly used to display dates in a format that differs to that the way it’s stored, or when joining two tables on columns that store the same data, but in different formats.

The CAST and CONVERT System Functions essentially achieve the same task, with a few small difference.

CAST is the ANSI compliant version of the two. CAST can change data from one type to another, but cannot change the format of a date. It’s simple to use, and it’s syntax is as follows:

CAST ( Expression AS Target Data Type )

Where I’ve written “Expression”, you would replace with either a column name, or a hardcoded value (Such as a value of 7). “Target Data Type” would be replaced with the name of the Data Type that you’d like to convert the value to.

CONVERT works in a similar manner, but has one more optional value available and is not ANSI standard.

CONVERT ( Target Data Type, Expression, Style )

As above, Target Data Type is the data type that you’d like to convert to, and Expression is the column or hardcoded value. You’ll notice that this example also includes Style. This option is available when working with dates. For example, most of the time dates are stored as a DateTime which would look like “2001-01-01 00:00:00:000″. If you wanted to exclude the time and display the date as “01 Jan 2001″ you’d use:

CONVERT ( VARCHAR(11), '2001-01-01 00:00:00:000', 106 )

Not all data types can be converted or cast to all data types, for example a Bit cannot be converted to an Image.

Below is a table of some of the more common Date Styles used, and an example of how the output will look. All of the examples use 2001-01-01.

Style Number Date Output Example
101 01-01-2001 (MM/DD/YYYY)
103 01-01-2001 (DD/MM/YYYY)
106 01 Jan 2001 (DD MON YYYY)
107 Jan 01, 2001 (MON DD, YYYY)
, , ,
Comment

Keyboard Shortcut to minimize result set in SSMS

October 29th, 2010 Posted in SQL Server Management Studio
Oct, 29 2010
No Comments »

If you’ve ever been working on a large stored procedure, or just a big chunk of SQL, then you know how annoying it is to have half of your screen taken up by the result set when your query doesn’t quite return the data you were expecting and need to go back to the drawing board!

Insteading of grabbing the result set and shrinking it down, then having to drag it back up when you re-execute your code, use this command:

CTRL + R

The same command will also bring the result set back up on screen should it already be minimized!

, ,
Comment

Keyboard Shortcut to comment code in SSMS

October 27th, 2010 Posted in SQL Server Management Studio
Oct, 27 2010
No Comments »

I’m a bit of a junky when it comes to keyboard shortcuts, and I’ve been wondering for awhile if there’s a shortcut available to comment out code in SQL Server Management Studio.

It turns out that there is!

To comment the code:

  • Highlight the rows that you would like to comment
  • Press CTRL + K, then CTRL + C

To un-comment the code:

  • Highlight the rows that you would like to un-comment
  • Press CTRL + K, then CTRL + U
, ,
Comment

Learning SQL

October 22nd, 2010 Posted in Books and Learning
Oct, 22 2010
2 Comments »

Inspired by Chris Sparshott’s post on Learning CSS, I decided to write about how I first got involved with SQL, how I learned, and how I continue to learn about its features.

First, a bit about the language. SQL stands for Structured Query Language, and it’s used to query databases to get the information you want and need from them.

Microsoft SQL, also known as “Sequel”, is my langauge of choice, and will be the main feature of my blog. There are several other database technologies which I may also touch on from time to time, they are Oracle, MySQL, Sysbase, and DB2 to name a few.

I first started to learn the language and syntax when I joined One2Create in November 2008. Previous to this I had very little experience in any development languages. I had learned bits and pieces of HTML, CSS and C#, but had never before used SQL.

The first thing I did was buy a book, this one in fact: Sams Teach Yourself SQL in 10 Minutes

I read through this book, and completed the examples as I went. It’s a complete novice’s book, and the author (Ben Forta) has done a very good job intoducing the language. It gives you more than enough to get an understanding in the basic syntax, features, and uses.

After about a year and a half of using Ben’s book as a reference, and gaining experiance in the industry, I decided I wanted to study for the Microsoft 70-433 SQL Developer 2008 exam.

My work purchased the 70-433 Self-Paced Training Kit Book to allow me to study the more advanced features of SQL in the hope that I could pass the exam. I’ve yet to attempt the test, but hope to in the near future.

Development languages, including SQL, are ever growing. There will always be more to learn, new features introduced with new versions, as well as keeping up with all of the old stuff! And this is why I love what I do!

, ,
Comment

SQL Joins – Left, Right, Inner, Full, and Cross!

October 19th, 2010 Posted in SQL
Oct, 19 2010
2 Comments »

SQL Joins are incredibly useful, and knowing the difference between the five different types makes harnessing their great power a lot easier… obviously!

Joins are used to join tables together in a query, so that you can use data from both tables. You are able to join to a maximum of 256 tables, which frankly, should be more than enough!

The syntax goes like this:

SELECT *
FROM Person AS P INNER JOIN
     Orders AS O ON P.PersonId = O.PersonId

The above example uses the most common of all joins, the INNER JOIN. The word “Inner” can actually be omitted from the query, as it isn’t necessary.

The Inner Join will join the two tables together on the linking columns, and only the results where a match is found in both tables will be returned.

Next, we have the OUTER JOIN’s (Left, Right, and Full). These are just as simple to use as the INNER join, but work a little differently. The Synax is the same as the above INNER join example, just replace “INNER JOIN” with “LEFT OUTER JOIN” or Right / Full, depending on what you want to achieve.

Again, with the OUTER Join’s, the word “Outer” can be omitted.

The way the LEFT JOIN works is by selecting all of the data from the table on the left, and returns all of the matchs from the table on the right. If there isn’t a match, then a NULL will be returned. Right Join is exactly the same, just reversed (returns everything on the right, and all possible matches on the left).

FULL JOIN will return every row from the left and right tables, matching where possible.

And lastly we have CROSS JOIN. This join will match every row from the left table, to every row in the right table. This means that if the left table has 100 rows, and the right table also has 100 rows, our result set would be a grand total of 10,000 rows! Can I think of an example where you would use this? No.

Hopefully the below simple diagram will help re-enforce what I’ve said above.

Joins Diagram

, , , , , ,
Comment

SQL LIKE Clause Syntax

October 18th, 2010 Posted in SQL
Oct, 18 2010
2 Comments »

The SQL LIKE clause is a very handy little tool, and it’s a very simple one to use.

A LIKE clause is a wildcard search placed in the WHERE clause, generally used to find a range of data by searching for a character string within a column.

There are several different searchs you can run with a LIKE, and I’ll explain each of them below.

The most common is probably the percent (%) symbol. The % replaces zero or more characters in a string, then searches for matchs of your string.

For example SELECT FirstName FROM People WHERE FirstName LIKE '%ach' would return ‘Zach’ and anything else that ends in “ach”. The % can be used on either side of the string, or on both sides. You also don’t actually need to specify a string (LIKE ‘%%’), this will return absolutely everything. I can’t see a use for this myself, but it’s there if you need it!

The next wildcard search is the underscore (_). This replaces one character in a character string. For instance LIKE ‘_at’ would return “At”, “Eat”, “Cat”, “Hat”, “Sat”.. etc.

Square Brackets ([ ]) is a third option. Within the square brackets you specify a range or set of characters which will replace a single character within a string. There are a couple of ways to use the square brackets as below;

LIKE '[abcd]'
LIKE '[a,b,c,d]'
LIKE '[a-d]'

All of the above would return the exact same results. In example one, we are specifying all of the characters we would like to look for in our string. Example two is exactly the same, but has comma’s between the letters to improve readability. Example three is slightly different, instead of specifying the individual characters, we are using a range, which will find anything that equals the two characters you input, and anything inbetween.

The above square bracket examples are actually fairly useless unless you store single letters in a column. To get around this, you can combine the symbols to give you more flexibility in your search.

LIKE '[a-e]%' would return anything that starts with the letters a-e (Air, Beer, Cricket, Dozen, Easy are all examples of what this search would return).

LIKE '[0-9][0-9][0-9]' can be used to confirm that a value is a 3 digit number.

The last symbol available to the LIKE clause is the caret (^). This can be used in conjuction with square brackets to rule out a set or range of characters.

LIKE '[^a-e]%' will return anything that starts with F-Z.

The NOT key word can also be specified with the LIKE clause to do a wildcard search for things that aren’t similar to your search conditions

NOT LIKE '%Stagers%' would return anything that doesn’t contain the character string “Stagers”.

The NOT keyword also gives you the ability to create a double negative! Again, this is a little pointless, but it’s possible!

NOT LIKE '[^a-d]%' would return anything that starts with an A, B, C, or D.

,
Comment
Older Entries