Apply built-in scalar functions

May include but is not limited to: CAST and CONVERT; REPLACE; LEN and DATALENGTH; PATINDEX and CHARINDEX

You can define functions in SQL Server for enhancing your select or where clauses. These functions are called user-defined functions (a shocking name for them). However, there are many-many predefined functions you can work with, and they are called built-in functions. Now we’ll take a closer look on them.

The main purpose of these scalar functions is to enhance your select statements. You can also use them in your where clause, but with a considerable performance overhead, so try to restrict yourself here.

Because there are so many built-in functions, it’s a good idea to categorize them. Microsoft did this for us, so we know date/time functions,  system functions, string functions, and additional functions.

Date and time functions

The new ones here are SYSDATETIME, SYSUTCDATETIME and SYSDATETIMEOFFSET. These retrieve the actual system datetime in a format which is 100 nanoseconds precise. Quite a good way to measure time. But sometimes, you’ll be happy with less precision and in this case, you should consider GETDATE, GETUTCDATE and CURRENT_TIMESTAMP. GETDATE and CURRENT_TIMESTAMP produces the same output.

Other functions are the DATEADD, DATEDIFF, DATENAME, DATEPART. For example, if you need to get how many days passed since a date, you’d use:

DATEDIFF(DD, GETDATE(), ‘01/01/2001’)

System functions

System functions are used to change data format. The two most important ones are CAST and CONVERT. The main difference is that CONVERT is SQL Server specific, while CAST is part of the ANSI standard. CAST must be used when you work with decimals and floats, while you can work with dates and times by using CONVERT. Syntax:

CAST(value AS type)
CONVERT(type, value, (optional date-style))

String functions

String functions are used when dealing with textual data. Nice examples are LEN which returns the length of a string, SUBSTRING, which is good for slicing up a text, REPLACE, which needs no comment, but we have UPPER and LOWER, too.

A nice example on SUBSTRING:

SELECT SUBSTRING(‘This is a good sentence’, 4, 15)

This one returns the string ‘a is a good sen’.

Additional functions

Finally, the ones who don’t fit anywhere else. A good example is CHARINDEX, which returns the position of an occurrence in a text, or PATINDEX, which does the same with patterns.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s