SQL Server Builtin Functions

Welcome People, Bots & Algo’s of THE INTERWEBS. This is the Mondays Edition of The Analyst.

All of the functions that SQL Server ships with can be found inside of your database.

You can find that by expanding the programmability folder and then going into the functions folder and then expanding the systems functions folder.

?

This will reveal a number of different categories. Now if you've ever used Excel, these are comparable to the formula books that organize all of the functions in that program.

?

Expanding each of these folders, for example, the aggregate functions folder, will reveal the individual functions that we can use in our SQL scripts.

When you hover your mouse over one of these functions you'll get this little pop-up menu that gives you a brief description about what this function does and if you expand the function by clicking on the little plus to the left of it, it'll give you information about the parameters.

For instance this counting function will take any data type as an input, text numbers, dates, it doesn't matter and it'll return an integer counting up how many items there were.

Now if you're doing scientific or statistical work then the standard deviation function, which you can find right here, or the variance function might be useful for your work, or if you're dealing with financial data, perhaps selling revenue across sales regions with the sum function, will provide some interesting insights into your business. As you dig through these different folders you can see that there's dozens and dozens of different functions to choose from and determining which ones will be useful for you is highly dependent on the types of data that you're working with and the kinds of analysis that you need to perform.

?

Since most functions work in exactly the same way, let me show you with just a couple that can be found inside of these string functions category.

?

Example using the String Functions

?

we'll start with a simple select statement, just select first name, last name from the guest table.

that returns the data exactly as its stored in our underlined data tables.

SELECT FirstName, LastName
FROM Guests;
        

UPPERCASE VERSION

Now if I wanted to format this data in all upper case characters for example for maybe a mailing list or some other use, then I can simply wrap these column names with a function called upper.

SELECT Upper(FirstName) AS FirstName, Upper(LastName) AS LastName
FROM Guests        

This time when I execute the query it returns the first name and the last name data converted into all uppercase characters. This function makes it easy to format the text as you need it and you can quickly move on to your next task. Some functions require multiple input parameters.

?

For instance there's also a function called left that extracts characters from the left side If I change the upper function to just say left, message, it says that the left function requires two arguments.

In order to use left, we need to specify how many characters we're going to extract from the left side of the string.

LEFT VERSION

which for the left function is an imager. Let's say I want to extract just the first character from the first name, but I want to see three characters from the left side of the last name, this time when I execute the query I'm seeing just the first initial from the first name and the first three characters from that last name.

SELECT LEFT(FirstName, 1) AS FirstName, LEFT(LastName, 3) AS LastName
FROM Guests        

So that's just two simple examples on how to use functions to modify how we would view the data that's stored in our tables.

COMBINING FUNCTIONS

Certain function you can combine with each other

LEFT WITH UPPER

Say you want the First two letters of the first name and last name to be capitalized

SELECT UPPER (LEFT(FirstName, 2)) AS FirstName, UPPER(LEFT(LastName, 2)) AS LastName

FROM Guests        

?