August 05, 2012 SQL Tutor 0

SQL Server stores some special values in a function. A function is kind of like a stored procedure that runs in place and returns data that you need before processing the rest of the statement. You might see them in place of columns, in a where clause or anywhere else where you might see a variable or a literal value.

We've seen one of these before. The GETDATE() function tells us the current server date and time. You can use a function just like you use a variable in most cases. We've also used LEN and SUBSTRING in the string manipulation lessons. However, there are some other useful functions that you should add to your arsenal.

Print GETDATE()

This will return the current time. You might be wondering what those parenthesis at the end mean. Some functions have parameters like a stored procedure. We'll see how to use these as we get further into this lesson. In the next lesson we'll even make our own!

Some Useful System Functions

We can do a lot more than just get the date with functions. Here's some other handy SQL functions we can access whenever we need.

ISNUMERIC

The ISNUMERIC function evaluates a piece of data and lets us know if it's a number value or not. It returns a true or false value.

Syntax:

ISNUMERIC ([expression])

Example:

PRINT ISNUMERIC('One')
PRINT ISNUMERIC('1')

The first statement will return a false value, or a zero. The second will return a true value represented by a 1. This is handy for checking user input before trying to do something that will only work with a number.

IF ISNUMERIC(@UserInput) = 1
BEGIN
	ProcessNumber @UserInput
END
ELSE
BEGIN
	ProcessString @UserInput
END

In this example, we have a special stored procedure for dealing with numbers and one for dealing with strings. We use ISNUMERIC to figure out which one we have and then go from there.

ISNULL

NULL is a weird thing in SQL. It signifies a blank value. It's not the same thing as a space or an empty string. SQL sometimes does weird things with NULLS. For example, consider the following example.

DECLARE @MyVariable varchar(50)
DECLARE @MyBlank varchar(50)

SET @MyVariable = 'Hello'

PRINT @MyVariable + @MyBlank

You'd think this would display “Hello” but it actually displays nothing. What happened to “Hello”? SQL decided that NULL is not a valid string character, so it doesn't know how to add them together. Since we don't want things just vanishing like this, we need a way of handling nulls gracefully. It'd probably be okay to assume that null means a blank string or some other value in this situation. This is when we use ISNULL.

DECLARE @MyVariable varchar(50)
DECLARE @MyBLank varchar(50)

SET @MyVariable = 'Hello'

PRINT @MyVariable + ISNULL(@MyBlank, 'World')

Now, when we run this statement the NULL value is replaced with the word World. If we add a value to the variable, that value will display instead.

DECLARE @MyVariable varchar(50)
DECLARE @MyBLank varchar(50)

SET @MyVariable = 'Hello'
SET @MyBlank = 'Planet'
PRINT @MyVariable + ISNULL(@MyBlank, 'World')

This returns “Hello Planet.” Basically, the ISNULL gives us a safety net for something being blank when it should not be.

COALESCE

COALESCE is an odd sounding fucntion that does almost the same thing as ISNULL except for one crucial difference. ISNULL only lets you replace one value. COALESCE, on the other hand, lets you load up a whole list of possible backup values. If one is blank it moves to the next one until it finds a non-blank value to substitute.

DECLARE @One varchar(50)
DECLARE @two varchar(50)
DECLARE @three varchar(50)
SET @three = '2'

PRINT COALESCE (@One, @two, @three, 'Nothing!')

Since one and two are filled with NULLS, they get ignored and SQL puts in the value of three into the print statement. If they had all been blank, the “Nothing!” value would have displayed instead of the “2.” This function is very useful when adding contingencies for bad user data or blank variables.

NEWID

The NEWID function creates a new GUID, or globally unique identifier, that you can use to identify a new row in SQL or to otherwise label something with a new identity value.

SELECT NEWID()

This will return some large string value that looks like this

21EC2020-3AEA-1069-A2DD-08002B30309D

You can use this any time you need to assign IDs to something without creating a primary key and permanent ID scheme.

Example:

SELECT NEWID() as TempID, Date, TranAmount
Into #NewTable
FROM Transactions

This will create a new temporary table that contains three columns. The first column will be filled with randomly generated globally unique identifiers.

OBJECT_ID

In our previous lesson we had a situation where we needed to handle an error if a table did not exist. However, there is a great way to check if a table exists before taking any action. The OBJECT_ID function may not seem that useful since it just returns the numeric ID for a database object. However, the existence of this ID is enough to tell us that the object exists. If we get back a NULL, we know our object does not exist.

IF OBJECT_ID('dbo.MyTempTable') IS NULL THEN DROP TABLE MyTempTable

SELECT SpaceID, Space, CubeNumber, Code, CreatedDate, User
INTO dbo.MyTempTable
FROM SpaceTable

In this scenario, we could drop our table before creating in it the next step. This is a very common use for the OBJECT_ID function. We can't always be sure that a table got dropped as it was supposed to. If nothing in the table is too critical, we can make sure its not there before we cause an error by creating it.

LCASE and RCASE

We've already looked at some of the system functions we need to convert string data. Here's another useful one that lets you capitalize or remove the capitalization from your strings.

Print LCASE('ThIs Is my TeXt' )
Print UCASE('ThIs Is my TeXt' )

This will produce:

this is my text
THIS IS MY TEXT

As you can see, functions are a very powerful part of SQL. The coolest thing is that you can make your own functions in SQL server. Our next lesson will show you how to create your own functions that you can use in any SQL server code you write throughout your database.