Home

Introduction to Built-In Functions

 

Fundamentals of Built-In Functions

 

Introduction

To assist your development with the different tasks of a database, Transact-SQL ships with various already created and tested functions. You just need to be aware of these functions, their syntax, and the results they produce.

Introduction

To assist your development with the different tasks of a database, Transact-SQL ships with various already created and tested functions. You just need to be aware of these functions, their syntax, and the results they produce.

To help you identify the functions you can use, they are categorized by their types and probably their usefulness.

Because of their complexities, some values can be easily recognized or fixed. For example, a date such as January 6, 1995 is constant and can never change. This type of value is referred to as deterministic because it is always the same. In the same way, a time value such as 5PM is constant and cannot change. There are other values that cannot be known in advance because they change based on some circumstances. For example, the starting date of the school year changes from one year to another but it always occurs. This means that, you know it will happen but you don't know the exact date. Such a value is referred to as non-deterministic.

Fundamentals of Built-In Functions

To support determinism and non-determinism, Transact-SQL provides two broad categories of functions. A function that always returns the same or known value is referred to as deterministic. A function whose returned value may depend on a condition is referred to as non-deterministic.

We continue our university database. In this version, University4, we will add a table for the students. This table will be stored in the Academics schema.

Assistance With Data Entry

We plan to assist the accounting department with evaluating people’s salaries from their weekly work time and their hourly salaries. In the real world, most employees have a fixed salary but a few employees and contractors are paid on an hourly basis). Therefore, we will start performing some calculations based on employees’ information.

 

Practical LearningPractical Learning: Introducing Built-In Functions

  1. Start the computer and log in
  2. Launch Microsoft SQL Server and click Connect
  3. Right-click the server name and click New Query
  4. To create a new database, in the empty window, type the following:
    USE master;
    GO
    DROP DATABASE University2;
    GO
    DROP DATABASE University3;
    GO
    CREATE DATABASE University4;
    GO
    USE University4;
    GO
    CREATE SCHEMA Academics;
    GO
  5. To execute the SQL statement, press F5

Parsing an Expression or Value

Parsing consists of scanning, that is, checking every part (such as every symbol) of an expression or a word to match a pattern. An example of parsing is to check every symbol in a combination to find out if it is a number or something else. Based on this, parsing something like 275.25 would determine whether this is a decimal number or not.

To assist you with parsing, Transact-SQL provides a function named PARSE. Its syntax is:

PARSE ( string_value AS data_type [ USING culture ] )

This function takes one argument, passed as a string and accompanied by its data type preceded by the AS keyword. The function is used to "scan" an expression that is passed as the string_value argument. The expression must follow the rules of the data_type argument. For example, suppose you want to find out if some value is an integer. You can pass the data_type as int. Then, the expression passed as the argument must include digits and no other character or symbol. On the other hand, if you want to know whether some value is a date, you can pass the data_type as date (or datetime2) and pass that value as argument. In this case, the value must follow the rules of a date value as specified in the Date tab in the Customize Regional Options accessible from the Regional and Language Options of the Control Panel.

If the argument may include international characters or formats (Unicode), you should indicate the language, called a culture, that the argument follows.

If the PARSE() function is not able to determine the type or if the value of the argument doesn't follow the rule of the data_type, this function produces (throws) an error. As an alternative to PARSE(), Transact-SQL provides the TRY_PARSE() function. Its syntax is:

TRY_PARSE ( string_value AS data_type [ USING culture ] )

As you can see, this function uses the same argument as PARSE. The difference is that, while PARSE() produces an error if the parsing operation fails, TRY_PARSE produces NULL (if the parsing operation fails). This means that, in most cases, you should prefer TRY_PARSE() instead of PARSE().

Casting a Value

In most cases, a value the user submits to your database is primarily considered a string. This is convenient if that's what you are expecting. If the value the user provides must be treated as something other than a string, for example, if the user provides a number, before using such a value, you should first convert it to the appropriate type, that is, from a string to the expected type.

To assist you to convert a value, Transact-SQL provides the CAST() function. Its  syntax is:

CAST ( Expression AS DataType [ ( length ) ] )

The Expression is the value that needs to be cast. DataType is the type of value into which you want to convert the Expression. The DataType can be one of those we reviewed in Lesson 4.

If the CAST() function is not able to cast the expression (if it fails), it produces (throws) an error. As an alternative to CAST(), Transact-SQL provides a function named TRY_CAST. Its syntax is:

TRY_CAST ( expression AS data_type [ ( length ) ] )

This function takes the same arguments as CAST. The difference is that, instead of producing an error, if TRY_CAST() fails, it returns NULL. This also means that, in most cases, you should use TRY_CAST() instead of CAST.

Practical LearningPractical Learning: Casting a Value

  1. Click inside the Query Editor and press Ctrl + A to select everything
  2. Type the following:
    DECLARE @StrSalary nvarchar(10),
    	@StrHours  nvarchar(6),
    	@WeeklySalary decimal(6,2)
    SET @StrSalary = N'2W2.18';
    SET @StrHours  = N'38.50';
    
    SET @WeeklySalary = CAST(@StrSalary As Decimal(6,2)) *
    		    CAST(@StrHours As Decimal(6,2));
    SELECT @WeeklySalary;
    GO
  3. Press F5 to execute

    Casting a Value

  4. To avoid the error, change the statement as follows:
    DECLARE @StrSalary nvarchar(10),
    	@StrHours  nvarchar(6),
    	@WeeklySalary decimal(6,2)
    SET @StrSalary = N'2W2.18';
    SET @StrHours  = N'38.50';
    
    SET @WeeklySalary = TRY_CAST(@StrSalary As Decimal(6,2)) *
    		    CAST(@StrHours As Decimal(6,2));
    SELECT @WeeklySalary;
    GO
  5. Press F5 to execute

    Casting a Value

  6. Again, change the statement as follows:
    DECLARE @StrSalary nvarchar(10),
    	@StrHours nvarchar(6),
    	@WeeklySalary decimal(6,2)
    SET @StrSalary = N'242.18';
    SET @StrHours  = N'38.50';
    
    SET @WeeklySalary = TRY_CAST(@StrSalary As Decimal(6,2)) *
    	            TRY_CAST(@StrHours As Decimal(6,2));
    SELECT @WeeklySalary;
    GO
  7. Press F5 to execute

    Casting a Value

Converting a Value

Like CAST(), the CONVERT() function is used to convert a value. Unlike CAST(), CONVERT() can be used to convert a value from its original type into a non-similar type. For example, you can use CONVERT to cast a number into a string and vice-versa.

The  syntax of the CONVERT() function is:

CONVERT(DataType [ ( length ) ] , Expression [ , style ])
Converting a Value

The first argument must be a known data type, such as those we reviewed already. If you are converting the value into a string (varchar, nvarchar, char, nchar) or a binary type, you should specify the number of allowed characters in the data type's own parentheses, as the length argument.

As reviewed for the CAST() function, the Expression is the value that needs to be converted.

If the conversion is performed on a date or time value, the style argument is a number that indicates how that conversion must proceed.

If the CONVERT() function is not able to perform its operation, it produces an error.

Because of some difficulties that can result from conversion, Transact-SQL provides a function named TRY_CONVERT. Its syntax is:

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

The arguments are the same used in the CONVERT() function. The difference is that if the TRY_CONVERT() function fails, it returns NULL instead of producing (throwing) an error.

Practical LearningPractical Learning: Converting a Value

  1. Click inside the Query Editor and press Ctrl + A
  2. Type the following:
    -- Square Calculation
    DECLARE @Side As decimal(10,3),
            @Perimeter As decimal(10,3),
            @Area As decimal(10,3);
    SET     @Side = 48.126;
    SET     @Perimeter = @Side * 4;
    SET     @Area = @Side * @Side;
    PRINT N'Square Characteristics';
    PRINT N'-----------------------';
    PRINT N'Side      = ' + TRY_CONVERT(varchar(10), @Side, 10);
    PRINT N'Perimeter = ' + TRY_CONVERT(varchar(10), @Perimeter, 10);
    PRINT N'Area      = ' + TRY_CONVERT(varchar(10), @Area, 10);
    GO
  3. Press F5 to execute

Converting a Value

Transact-SQL Macros and Metadata Functions

 

Introduction to Macros

A macro is an action that can be performed on a database or certain parts of a database or of a database server. A macro resembles a function without being exactly one. Transact-SQL provides various macros to assist you with managing databases and their objects.

Checking the Existence of a Record

One of the simplest operations a user can perform on a table consists of looking for a record. To do this, the user would open the table that contains the records and visually check them, looking for a piece of information, such as a student's last name.

As the database developer, you too can look for a record and there are various techniques you can use. To assist you with this, Transact-SQL provides a macro named EXISTS. Its syntax is:

BIT EXISTS(SELECT Something)
Checking the Existence of a Record

This macro takes one argument. The argument must be a SELECT statement that would be used to get the value whose existence would be checked. For example, we know a system database named databases that contains a record of all databases stored on your server. You can use the EXISTS() macro to check the existence of a certain database.

Introduction to Metadata Functions

A metadata is a piece of information held by an object. Microsoft SQL Server (or rather Transact-SQL) provides many functions that use the concept of metadata to provide information about many objects of a database or of a database server.

Getting the Identifier of an Object

A database contains many types of objects such as tables, etc. When you create one of them, the database engine creates an internal identifier for it (and stores that name somewhere). This allows the database engine to identify all objects stored in all databases. To let you know the identifier of an object, Transact-SQL provides a function named OBJECT_ID. Its syntax is:

OBJECT_ID('[ database_name . [ schema_name ] . | schema_name . ] 
 	  object_name' [ ,'object_type' ]);

This function takes two arguments that are passed as strings. The first argument is required and the second is optional. The first argument, object_name, is the name of an object such as a table. Here is an example:

SELECT COLUMNPROPERTY(OBJECT_ID(N'Employees'),
      		      N'LastName',
      		      N'precision')AS [Maximum Number of Characters];
GO

If the object belongs to a schema (other than dbo), precede its name with it. You can also completely qualify the name of the object by preceding the schema with the name of its database.

The second argument of the OBJECT_ID() function allows you to specify the type of object of the object_name argument. It is passed as one or two letters depending on the object. For a table, the argument is passed as 'U'.

The Identifier of a Database

As mentioned in Lesson 2, every time you create a new database, it gets added to the master system database and your new database can be located using sys.databases. Also, when the new database is added to the master database, it automatically receives an integer that uniquely identifies it.

To know the identifier of a database, you an call the DB_ID() function. Its syntax is:

DB_ID ( [ 'database_name' ] ) RETURNS int;

This function takes an argument that is optional. If you call the function without an argument, it returns the name of the current database (the database that is currently selected). Here is an example:

SELECT DB_ID() AS [Selected Database];
GO

To find out the identifier of an existing database, pass the name of that database as argument. Here is an example:

SELECT DB_ID(N'KoloBank') AS [ID For Kolo Bank];
GO

In reality, when you pass an argument, the database engine would look for a database with that name. If the database is found, the function returns its number. If there is no database with that name, the function returns NULL (it doesn't produce an error).

Getting the Current Database

At any time, to find out the name of the database that is currently used, call the DB_NAME() function. Its syntax is:

DB_NAME ( [ database_id ] ) RETURNS nvarchar(128);

This function takes an optional argument. If you call this function without an argument, it finds the name of the database that is currently selected and produces it. Here is an example:

SELECT DB_NAME();
GO

If you want, pass an integer that represents the index (in the normal English sense, not in database sense) of the database. Normally, here are the databases produced by some of the arguments:

If you pass the argument as The function would produce
1 master
2 tempdb
3 model
4 msdb
5 ReportServer
6 ReportServerTempDB

The other numbers would ask the database engine to get the database that corresponds to the number of the argument. In fact, you can use a call to DB_ID and pass it as argument. Here is an example:

SELECT DB_NAME(DB_ID(N'KoloBank')) AS [Kolo Bank];
GO

The Current User

Transact-SQL provides various functions to let you know the name of the current user, that is, the user name of the person who is currently logged on. To let you get the identifier of the user, you can call the SUSER_ID or the SUSER_SID function. Their syntaxes are:

SUSER_ID ( [ 'login' ] ) RETURNS int;
SUSER_SID ( [ 'login' ] ) RETURNS varbinary(85);

These functions produce a number that identies the user but that value may not be particularly significant to you. Here is an example of calling the SUSER_ID() function:

Current User

To get the actual username of the user, you can call either the SUSER_NAME or the SUSER_SNAME function. Their syntaxes are:

SUSER_NAME ( [ server_user_id ] ) RETURNS nvarchar(128);
SUSER_SNAME ( [ server_user_sid ] ) RETURNS nvarchar(128)

The Name of the Computer

To get the name of the computer that is currently being used, you can call the HOST_NAME() function. Its syntax is:

HOST_NAME() RETURNS nvarchar(128);

Here is an example:

The Name of the Computer

Fundamentals of String-Based Functions

 

Introduction

The string is the most basic or the primary value that is presented to a database. This is because any value, before being treated particularly, is firstly considered a string. In an application, there are various ways you use or get a string. You can get it or provide it to a function as a constant string, that is, a string whose value you know certainly and that you pass to a function. You can also get a string that a user provides. Other functions also can produce or return a string.

To assist you with managing strings or performing operations on them, Transact-SQL provides various functions. The functions are divided in categories that include character-based, conversions, addition, sub-strings, etc.

The Length of a String

The length of a string is the number of characters or symbols it contains. To get the length of a string, you can use the LEN() function. Its syntax is:

int LEN(String)

This function takes one argument as the string to be considered. It returns the number of characters in the string. Here is an example:

DECLARE @FIFA nvarchar(120)
SET @FIFA = N'Fédération Internationale de Football Association'
SELECT @FIFA AS FIFA
SELECT LEN(@FIFA) AS [Number of Characters]

This would produce: 

Length

Concatenating Two Strings

Concatenating two strings consists of adding one string to the other. You can do this using the addition operator '+'. Here are examples:

DECLARE @FirstName nvarchar(24),
        @LastName nvarchar(24),
	@FullName nvarchar(50),
	@CompleteName nvarchar(50);
SET @FirstName = N'Jerome ';
SET @LastName = N'Ansfield';
SET @FullName = @FirstName + @LastName;
SET @CompleteName = N'Anne ' + N'Sanders';

SELECT N'Jeffrey ' + N'Erschbamer'; 
SELECT @FullName [Full Name];
SELECT @CompleteName [Complete Name];
GO

This would produce: 

Concatenating Two Strings

In the same way, you can concatenate various strings by using the addition operator between them. Here are examples:

DECLARE @FirstName nvarchar(24),
	@LastName nvarchar(24),
	@FullName nvarchar(50),
	@CompleteName nvarchar(50);
SET @FirstName = N'Jerome';
SET @LastName = N'Ansfield';
SET @FullName = @LastName + N', ' + @FirstName;
SET @CompleteName = N'Anne' + N' ' + N'Juliette' + N' ' + N'Sanders';

SELECT @FullName [Full Name];
SELECT @CompleteName [Complete Name];
SELECT N'Jeffrey' + N' ' + N'Anselme' + N' ' + N'Erschbamer'; 
GO

This would produce: 

Concatenating Two Strings

Transact-SQL provides a function that performs string concatenation. It is named CONCAT and its syntax is:

CONCAT(string string_value1,
       string string_value2
       [, string string_value_N]) RETURNS string;

This function taks an unlimited number of strings as arguments. This means that the function can be used to concatenate two or more strings. The function returns a string. In our formula, we used string as a data type. In reality, each of the arguments can be a char or one of its variants (nchar, char(n), varchar, nvarchar, or nvarchar(n), or nvarchar(max).

Here are examples where the function concatenates two strings:

DECLARE @FirstName nvarchar(24),
        @LastName nvarchar(24),
	@FullName nvarchar(50),
	@CompleteName nvarchar(50);
SET @FirstName = N'Jerome ';
SET @LastName = N'Ansfield';
SET @FullName = CONCAT(@FirstName, @LastName);
SET @CompleteName = CONCAT(N'Anne ', N'Sanders');

SELECT CONCAT(N'Jeffrey ', N'Erschbamer'); 
SELECT @FullName [Full Name];
SELECT @CompleteName [Complete Name];
GO

Here is another example where the function takes more than two arguments:

DECLARE @FirstName nvarchar(24),
	@LastName nvarchar(24),
	@FullName nvarchar(50),
	@CompleteName nvarchar(50);
SET @FirstName = N'Jerome';
SET @LastName = N'Ansfield';
SET @FullName = CONCAT(@LastName, N', ', @FirstName);
SET @CompleteName = CONCAT(N'Anne', N' ', N'Juliette', N' ', N'Sanders');

SELECT @FullName [Full Name];
SELECT @CompleteName [Complete Name];
SELECT CONCAT(N'Jeffrey', N' ', N'Anselme', N' ', N'Erschbamer'); 
GO

Practical LearningPractical Learning: Concatenating Two Columns

  1. Cclick iniside the Query Editor and press Ctrl + A
  2. To create a new table, type the following:
    USE University4;
    GO
    
    CREATE TABLE Academics.UndergraduateStudents
    (
        StudentNumber nvarchar(10) not null,
        FirstName nvarchar(20),
        MiddleName nvarchar(20),
        LastName nvarchar(20),
        EmployeeName AS CONCAT(LastName, N', ', FirstName),
        Username nvarchar(12),
        [Password] nvarchar(20) DEFAULT N'Password1',
        Gender nvarchar(20)
    );
    GO
  3. To execute the SQL statement, press F5
 
 
 

Strings and Type Conversion

 

Formatting a Value

In Transact-SQL and Microsoft Windows, there is a default way that each type of value should display. Sometimes you want the value to display some other way. To assist you with this, Transact-SQL provides the FORMAT() function (the function is actually borrowed from the .NET Framework). Its syntax is:

FORMAT(Type value, nvarchar format [, nvarchar culture ] ) RETURNS nvarchar

The FORMAT() function allows you to specify how you want a value to be presented to a user. This function can be applied to various types of values such as integers, floating-point numbers, date, or time. Therefore, the first argument of this function depends on the type of value you are using. Again, it can be a number, a date/time, or their variances.

The second argument specifies the format you want to apply to the first argument. The third argument is optional. It is important to keep two issues in mind:

  • The second argument is passed as a string
  • The format used in the second argument depends on the type of value of the first argument
  • The format used in the second argument should follow the rules of the language for which it is used. But, because this function can be flexible in its interpretation of the second argument, in which case it can allow you to go crazy in the type of format you are using (especially for date values), you should use a format that would be easily understood by the user

Probably the primary types of values you would use are natural numbers. You can display such numbers with their digits only. Here is an example:

DECLARE @YearlySalary int,
        @FiscalBudget bigint;

SET @YearlySalary = 48626;
SET @FiscalBudget = 12640685;

SELECT @YearlySalary AS "Yearly Salary",
       @FiscalBudget AS "Company Budget";
GO

In some cases, you may want to display such values as hexadecimal numbers. In US English, when the number is over 1000, you can separate the thousands with commas. To indicate this, you can call the FORMAT() function. Pass the value as the second argument and pass the second argument as:

  • G or g: This format, referred to as general, asks the database engine to refer to the way the data type of the variable or column is set in the internal libraries of Microsoft SQL Server. In most cases, the number is presented the same way it was assigned to the variable or set for the column. Here are examples:

    Value Formatting

  • D or d: This is used for natural numbers. The variable must have been declared using an integer type or the data of the column must have been set to an integer type. The function would display the number without a decimal part. Here are examples:
    DECLARE @HourlySalary int,
            @Distance real;
    
    SET @HourlySalary = 28.65;
    SET @Distance = 146.704;
    
    SELECT FORMAT(@HourlySalary, N'D') AS "Hourly Salary",
           FORMAT(@Distance, N'D') AS Distance;
    GO

    Value Formatting

  • X or x: A hexadecimal number is an integer value that can be made of letters from A to F and digits, in any combination. The variable or column must be of integer type. The other types are ignored (the function would produce NULL). To support this, you can pass the format as X or x. Here are examples:

    Value Formatting

  • F or f: A number is referred to as fixed if it displays with a specific number of digits in the decimal part. By default, that number is set to 2 digits (regardless of the number of digits set on the number). Here are examples:

    Value Formatting

  • P or p: One way to display fractional representation is as a percentage value. To support this, the FORMAT() function can use the P or p form. When using this format, the data type of the variable or column is important, so is the way the number was assigned to the variable or given to the column. For example, to treat the number as a fraction of 100, the value should be in the form of 0.X, which is 0 followed by a period and any number of digits. Here are examples:
    DECLARE @DiscountRate int,
            @RateOfDiscount int,
            @DiscountPart real,
            @InterestRate decimal(8, 4);
    
    SET @DiscountRate = 25.50;
    SET @RateOfDiscount = 0.2550;
    SET @DiscountPart = 0.2550;
    SET @InterestRate = 0.1295181;
    
    SELECT FORMAT(@DiscountRate, N'P') "Discount Rate",
           FORMAT(@RateOfDiscount, N'P') "Discount Rate",
           FORMAT(@DiscountPart, N'P') "Discount Rate",
           FORMAT(@InterestRate, N'p') N'Interest Rate';
    GO

    Value Formatting

  • N or n: Most languages, including US English, make it easy to read a large number by creating groups of digits. One way to group thousand digits is set them in sections of 3 when necessary. Here are examples of using the N or n format:

    Value Formatting

  • E or e: This is used to display the exponential part of a number, which is a number in the form X.XXXXXXE+00X. Each X represents a digit. The period, the E letter, and the +o r - sign is used. Here are examples:
    DECLARE @HourlySalary int,
            @UnitPrice decimal(8, 4),
            @FiscalBudget bigint;
    
    SET @HourlySalary = 28.65;
    SET @UnitPrice = 349.95;
    SET @FiscalBudget = 12640685;
    
    SELECT FORMAT(@HourlySalary, N'E') AS "Hourly Salary",
           FORMAT(@UnitPrice, N'e') [Unit Price],
           FORMAT(@FiscalBudget, N'E') AS N'Company Budget';
    GO

    Value Formatting

  • C or c: This is used for currency values. In US English, the number will be preceded with the $ symbol and:
    • If the number doesn't contain a decimal fraction (whether it is a natural or floating-point number), a fraction of .00 would be added to it
    • If the number is higher than 999, its thousands would be separated by commas
    Here are examples:
    DECLARE @HourlySalary int,
            @UnitPrice decimal(8, 4),
            @FiscalBudget bigint;
    
    SET @HourlySalary = 28.65;
    SET @UnitPrice = 349.95;
    SET @FiscalBudget = 12640685;
    
    SELECT FORMAT(@HourlySalary, N'C') AS "Hourly Salary",
           FORMAT(@UnitPrice, N'C') [Unit Price],
           FORMAT(@FiscalBudget, N'c') AS N'Company Budget';
    GO

    Value Formatting

  • If you pass any letter or format other than those reviewed above, it would be ignored. Also, if one of the above letters is used on the wrong value, the function would produce NULL. Here are examples:

    Value Formatting

By default, when you call the FORMAT() function, the database engine refers to the language used on the computer and the settings of the Control Panel. If you want to use the formats of another language, pass the third argument, culture. This argument, passed as a string, specifies the language whose rules the function should follow. Here are examples:

DECLARE @HourlySalary money,
        @Wage money,
        @UnitPrice decimal(8, 4),
        @FiscalBudget bigint;

SET @HourlySalary = 28.65;
SET @Wage = 28.65;
SET @UnitPrice = 349.95;
SET @FiscalBudget = 12640685;

SELECT FORMAT(@HourlySalary, N'C', N'en-CA') AS "Hourly Salary",
       FORMAT(@Wage, N'C', N'fr-ca') AS "Hourly Salary",
       FORMAT(@UnitPrice, N'c', N'en-gb') [Unit Price],
       FORMAT(@FiscalBudget, N'C', N'es-ES') AS N'Company Budget';
GO

Value Formatting

Converting From Integer to ASCII

As you may know already, a string is primarily one or a group of characters. These characters are ASCII values. If you have a string, to get the ASCII code of its leftmost character, you can call the ASCII() function. Its syntax is:

int ASCII(String)

This function takes as argument a string and returns the ASCII code of the first (the left) character of the string. Here is an example:

DECLARE @ES varchar(100)
SET @ES = N'El Salvador'
SELECT @ES AS ES
SELECT ASCII(@ES) AS [In ASCII Format]

This would produce: 

ASCII

Converting From ASCII to Integer

If you have the ASCII code of a character and want to find its actual character, you can call the CHAR() function. Its syntax is:

CHAR(int value) RETURNS char;

This function takes as argument a numeric value as an integer. Upon conversion, the function returns the ASCII equivalent of that number.

Converting to Lowercase

As you may know already, a string can be made of uppercase letters, lowercasel letters, and symbols that don't have a particular case. When you receive a string, if you want to convert all of its characters to lowercase, you can call the LOWER() function. Its syntax is:

LOWER(String) RETURNS varchar;

This function takes as argument a string. Any lowercase letter that is part of the string would not change. Any letter that is part of the string would be converted to lowercase. Any other character or symbol would be kept "as is". After conversion, the LOWER() function returns a new string. Here is an example:

DECLARE @FIFA varchar(120)
SET @FIFA = N'Fédération Internationale de Football Association'
SELECT @FIFA AS FIFA
SELECT LOWER(@FIFA) AS Converted

This would produce:

Lowercase Conversion

Practical LearningPractical Learning: Converting a String to Lowercase

  1. Click in the Query Editor and press Ctrl + A to select the code
  2. To create a function, type the following:
    USE University4;
    GO
    -- ===================================================================
    -- Function: CreateUsername
    -- This function creates a user name based on the first and last names
    -- ===================================================================
    
    CREATE FUNCTION Academics.CreateUsername
    	(@FirstName nvarchar(25), 
    	 @LastName nvarchar(25))
    RETURNS varchar(50)
    AS
    BEGIN
    	DECLARE @Username AS nvarchar(50);
    	SELECT @Username = LOWER(@FirstName) + LOWER(@LastName);
    	RETURN @Username;
    END
    GO
  3. Press F5 to execute the statement
  4. Press Ctrl + A to select everything
  5. To test the function, type the following:
    SELECT University4.Academics.CreateUsername(N'Francine', N'Mukoko');
    GO
  6. Press F5 to execute the statement
     
    Function

Sub-Strings

   

The Starting Characters of a String

A sub-string is a section gotten from a string. The idea is to isolate one or a group of characters for any necessary reason.

A left sub-string is one or a group of characters retrieved from the left side of a known string. To get the left sub-string of a string, you can use the LEFT() function. Its syntax is:

LEFT(String, NumberOfCharacters) RETURNS varchar

This function takes two arguments. The first argument specifies the original string. The second argument specifies the number of characters from the most-left that will constitute the sub-string. After the operation, the LEFT() function returns a new string made of the left character + the NumberOfCharacters on its right from the String.

Practical LearningPractical Learning: Creating a Sub-String With Left Characters

  1. Change the code in Query Editor to use the LEFT() function as follows:
    USE University4;
    GO
    DROP FUNCTION Academics.CreateUsername;
    GO
    CREATE FUNCTION Academics.CreateUsername
    	(@FirstName varchar(25), 
    	 @LastName varchar(25))
    RETURNS varchar(26)
    AS
    BEGIN
    	DECLARE @Username AS varchar(26);
    	SELECT @Username = LOWER(LEFT(@FirstName, 1) + 
    			   LEFT(@LastName, 4));
    	RETURN @Username;
    END
    GO
  2. Press F5 to execute the statement
  3. Click inside the Query Editor
  4. To test the function, type the following:
    SELECT University4.Academics.CreateUsername(N'Francine', N'Mukoko');
    GO
  5. Execute the statement in the window
     
    Function
  6. Change the call with a last name shorter than 5 characters such as "Um" and execute the statement. Here is an example:
     
    Function
  7. Click inside the Query Editor and press Ctrl + A
  8. To use the function, type the following:
    USE University4;
    GO
    
    INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, Username, Gender)
    VALUES(N'25506037', N'Matthew', N'Andy', N'Agurs', Academics.CreateUsername(N'Matthew', N'Agurs'), N'Male'),
          (N'88167413', N'Thomas',  N'Harold', N'Aldredge', Academics.CreateUsername(N'Thomas', N'Aldredge'), N'Male');
    GO
    INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, Username, Gender)
    VALUES(N'63358761', N'Martin',  N'Aldredge', Academics.CreateUsername(N'Martin', N'Aldredge'), N'Male'),
          (N'92739470', N'Maggie', N'Altobello', Academics.CreateUsername(N'Maggie', N'Altobello'), N'Female');
    GO
    INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, Username, Gender)
    VALUES(N'64702716', N'Frank', N'Alton', Academics.CreateUsername(N'Frank', N'Alton'), N'Male'),
          (N'25117160', N'Freida', N'Alvarez', Academics.CreateUsername(N'Freida', N'Alvarez'), N'Female');
    GO
    INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, Username)
    VALUES(N'30493386', N'Hahia', N'Kwok', Academics.CreateUsername(N'Hahia', N'Kwok'));
    GO
    INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, Username, Gender)
    VALUES(N'69631417', N'Simonne', N'Alvaro', Academics.CreateUsername(N'Simonne', N'Alvaro'), N'Female');
    GO
    INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, Username, Gender)
    VALUES(N'76088478', N'Tricia', N'Catherine', N'Anderson', Academics.CreateUsername(N'Tricia', N'Anderson'), N'Female');
    GO
    INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, Username, Gender)
    VALUES(N'77630549', N'Patrick', N'Amaleck', Academics.CreateUsername(N'Patrick', N'Amaleck'), N'Male'),
          (N'29502309', N'Maria', N'Anderson', Academics.CreateUsername(N'Maria', N'Anderson'), N'Female');
    GO
    INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, Username, Gender)
    VALUES(N'68903849', N'Katherine', N'Jennie', N'Sandford', Academics.CreateUsername(N'Katherine', N'Sandford'), N'Female');
    GO
  9. Press F5 to execute

The Ending Characters of a String

Instead of the starting characters of a string, you may want to create a string using the most-right characters of an existing string. To support this operation, Transact-SQL provides the RIGHT() function. Its syntax is:

RIGHT(String, NumberOfCharacters) RETURNS varchar;

This function takes two arguments. The first argument specifies the original string. The second argument specifies the number of characters from the most-right that will constitute the sub-string.

Practical LearningPractical Learning: Getting the Right Characters

  1. Click inside the Query Editor and press Ctrl + A
  2. To create a function, type the following:
    -- ==================================================
    -- Function: Last4DigitsOfSSN
    -- Description: This function finds the last 4 digits 
    -- 		of a US Social Security Number
    -- ==================================================
    
    CREATE SCHEMA Administration;
    GO
    CREATE FUNCTION Administration.Last4DigitsOfSSN(@SSN nvarchar(12))
    RETURNS nchar(4)
    AS
    BEGIN
    	RETURN RIGHT(@SSN, 4);
    END
    GO
  3. Press F5 to execute the statement
  4. Click inside the Query Editor and press Ctrl + A
  5. To test the function, type the following:
    SELECT University4.Administration.Last4DigitsOfSSN(N'836483846');
    GO
  6. Execute the statement
     
    Function

Replacing Occurrences in a String

One of the most annoying situations you may encounter with a string is to deal with one that contains unexpected characters. This could be due to its formatting or any other reason. For example, if you request a telephone number from a user, there are various ways the string could be presented to you. Examples are 000-000-0000, or 0000000000, or (000) 000-0000, or 000.000.0000. Every one of these formats is an acceptable US and Canadian telephone number but if you involve that string in an operation, you could get an unpredictable result.

Replacing Occurrences in a String

One way you can solve this type of problem is to remove any undesired characters from the string. This operation can also consist of replacing some character(s) with (an)other(s).

To replace one character or a sub-string from a string, you can call the REPLACE() function. Its syntax is:

REPLACE(String, FindString, ReplaceWith) RETURNS varchar;

or

REPLACE(String, FindString, ReplaceWith) RETURNS binary;

This function takes three arguments. The first is the string that will be used as reference. The second argument, FindString, is a character or a sub-string to look for in the String argument. If the FindString character or sub-string is found in the String, then it is replaced with the value of the last argument, ReplaceWith.

Practical LearningPractical Learning: Replacing Characters or Sub-Strings

  1. Click inside the Query Editor and press Ctrl + A
  2. To use the REPLACE() function, type the following code:
    DROP FUNCTION Administration.Last4DigitsOfSSN;
    GO
    CREATE FUNCTION Administration.GetLast4DigitsOfSSN(@SSN varchar(12))
    RETURNS char(4)
    AS
    BEGIN
    	DECLARE @StringWithoutSymbol As varchar(12);
    	-- First remove empty spaces
    	SET @StringWithoutSymbol = REPLACE(@SSN, N' ', N'');
    	-- Now remove the dashes "-" if they exist
    	SET @StringWithoutSymbol = REPLACE(@StringWithoutSymbol, N'-', N'');
    	RETURN RIGHT(@StringWithoutSymbol, 4);
    END
    GO
  3. Press F5 to execute the statement
  4. To test the function, type the following:
    SELECT University4.Administration.GetLast4DigitsOfSSN(N'244-04-8502');
    GO
  5. To execute the statement in the window, press F5

Arithmetic Functions

 

The Sign of a Number

In arithmetic, a number is considered as being negative (less than 0) or positive (equal to 0 or higher than 0). When a number is negative, it must have a - symbol to its left. If it is positive, it may display a + symbol to its left or it can omit it. A number without the - or + symbol to its left is considered positive, also referred to as unsigned. The symbol that determines whether a number is positive or negative is referred to as its sign. The sign is easily verifiable if you know the number already. In some cases, when a number is submitted to your application, before taking any action, you may need to get this piece of information.

To find out if a value is positive or negative, Transact-SQL provides the SIGN() function. Its syntax is:

SIGN(Expression)

This function takes as argument a number or an expression that can be evaluated to a number. The interpreter would then examine the number:

  • If the Expression is positive, the function returns 1. Here is an example:
     
    DECLARE @Number As int;
    SET @Number = 24.75;
    SELECT SIGN(@Number) AS [Sign of 1058];
    GO
    Sign
  • If the Expression is null, the function returns 0
     
    DECLARE @Number As int;
    SET @Number = 0;
    SELECT SIGN(@Number) AS [Sign of Number];
    GO
    The Sign of a NULL Number
  • If the Expression is negative, the function returns -1
     
    DECLARE @Number As int;
    SET @Number = -57.05;
    SELECT SIGN(@Number) AS [Sign of -57.05];
    GO
    The Sign of a Negative Number

Based on this, you can call the SIGN() function to find out whether a value is negative, null, or positive: simply pass the value (or a variable) to SIGN() and use a logical operator to check its sign.

The Absolute Value of a Number

The decimal numeric system counts from minus infinity to infinity. This means that numbers are usually negative or positive, depending on their position from 0, which is considered as neutral. In some operations, the number considered will need to be only positive even if it is provided in a negative format. The absolute value of a number x is x if the number is (already) positive. If the number is negative, its absolute value is its positive equivalent. For example, the absolute value of 12 is 12, while the absolute value of -12 is 12.

To get the absolute value of a number, you can use the ABS() function. Its syntax is:

ABS(Expression) RETURNS Data Type of Argument

This function takes an expression or a number as argument and returns its absolute value. Here is an example:

DECLARE @NumberOfStudents INTEGER;
SET @NumberOfStudents = -32;

SELECT ABS(@NumberOfStudents) AS [Number of Students];
GO

This would produce:

Abs

The Ceiling of a Number

Consider a decimal value such as 12.155. This number is between integer 12 and integer 13

In the same way, consider a number such as -24.06. As this number is negative, it is between -24 and -25, with -24 being greater.

In algebra, the ceiling of a number is the closest integer that is greater than or higher than the number considered. In the first case, the ceiling of 12.155 is 13 because 13 is the closest integer greater than or equal to 12.155. The ceiling of -24.06 is -24.

To get the ceiling of a number, Transact-SQL provides the CEILING() function. Its syntax is:

CEILING(Expression)

This function takes as argument a number or an expression that can evaluate to a number. After the conversion, if the function succeeds, it returns a double-precision number that is greater than or equal to Expression. Here is an example:

DECLARE @Number1 As Numeric(6, 2),
		@Number2 As Numeric(6, 2)
SET @Number1 = 12.155;
SET @Number2 = -24.06;

SELECT CEILING(@Number1) AS [Ceiling of 12.155],
       CEILING(@Number2) AS [Ceiling of -24.06];
GO

This would produce:

Ceiling

Here is another way of displaying the above results:

DECLARE @Number1 As Numeric(6, 2),
	@Number2 As Numeric(6, 2)
SET @Number1 = 12.155;
SET @Number2 = -24.06;

PRINT N'The ceiling of 12.155 is ' + 
      TRY_CONVERT(varchar(10), CEILING(@Number1));
PRINT N'The ceiling of -24.06 is ' +
      TRY_CONVERT(varchar(10), CEILING(@Number2));
GO

This would produce:

The ceiling of a number

The Floor of a Number

Consider two decimal numbers such as 128.44 and -36.72. The number 128.44 is between 128 and 129 with 128 being the lower. The number −36.72 is between −37 and +36 with −37 being the lower. The lowest but closest integer value of a number is referred to as its floor. Based on this, the floor of 128.44 is 128. The floor of −36.72 is −37.

To support finding the floor of a number, Transact-SQL provides the FLOOR() function. Its syntax is:

FLOOR(Expression)

The FLOOR() function takes as argument a numeric value or an expression that can be evaluated to a number. If the function succeeds during its conversion, it produces the integer that is the floor of the argument. Here is an example:

DECLARE @Number1 As Numeric(6, 2),
	@Number2 As Numeric(6, 2);
SET @Number1 = 128.44;
SET @Number2 = -36.72;

SELECT FLOOR(@Number1) AS [Floor of 128.44],
       FLOOR(@Number2) AS [Floor of -36.72];
GO

This would produce:

The floor of a number

The Exponent of a Number

To calculate the exponential value of a number, Transact-SQL provides the EXP() function. Its syntax is:

EXP(Expression)

This function takes one argument as a number or an expression that can be evaluated to a number. Here is an example:

DECLARE @Number As Numeric(6, 2);
SET @Number = 6.48;

SELECT EXP(@Number) AS [Exponent of 6.48];
GO

This would produce:

Exponentiation

 

The Power of a Number

The power of a number is the value of that number when raised to another number. This is done using the following formula:

ReturnValue = xy

To support finding the power of a number, Transact-SQL provides the POWER() function. Its syntax is:

POWER(x, y)

This function takes two required arguments. The first argument, x, is used as the base number to be evaluated. The second argument, y, also called the exponent, will raise x to this value. Here is an example:

DECLARE @x As Decimal(6, 2),
	@y As Decimal(6, 2);
SET @x = 20.38;
SET @y = 4.12;
SELECT POWER(@x, @y) AS [Power of 20.38 raised to 4.12];
GO

This would produce:

Evaluating the Power of a Number

 

The Natural Logarithm of a Number

To assist with finding the natural logarithm of a number, Transact-SQL provides the LOG() function. Its syntax is:

LOG(Expression)

This function takes one argument as a number or an expression that can evaluate to a number. After the calculation, it returns the natural logarithm of the argument. Here is an example:

DECLARE @Number As Decimal(6, 2);
SET @Number = 48.16;

SELECT LOG(@Number) AS [Natural Logarithm of 48.16];
GO

This would produce:

Finding the Natural Logarithm of a Number

The Base-10 Logarithm of a Number

To calculate the base 10 logarithm of a number, Transact-SQL provides the LOG10() function. Its syntax is:

LOG10(Expression)

The number to be evaluated is passed as the argument X. The function returns the logarithm on base 10 using the formula:

y = log10x

which is equivalent to

x = 10y

Here is an example:

DECLARE @Number As Decimal(6, 2);
SET @Number = 48.16;

SELECT LOG10(@Number) AS [Base-10 Logarithm of 48.16];
GO

This would produce:

Log10

The Square Root

To support the calculation of a square root, Transact-SQL provides the SQRT() function. Its syntax is:

SQRT(Expression)

This function takes one argument as a positive decimal number. If the number is positive, after the calculation, the function returns the square root of x. Here is an example:

DECLARE @Number As Decimal(6, 2);
SET @Number = 48.16;

SELECT SQRT(@Number) AS [The square root of 48.16 is];
GO

This would produce:

Sqrt

If the number is negative, you would receive an error. Here is an example:

Sqrt

In this case, you can use a control statement to find out whether the Expression is positive.

Measure-Based Functions

 

Introduction

 

A circle is a series of distinct opposite points positioned each at an exact same distance from another point referred to as the center. The distance from the center C to one of these equidistant points is called the radius, R. The line that connects all of the points that are equidistant to the center is called the circumference of the circle. The diameter is the distance between two points of the circumference to the center. In other words, a diameter is double the radius.

To manage the measurements and other related operations, the circumference is divided into 360 portions. Each of these portions is called a degree. The unit used to represent the degree is the degree, written as °. Therefore, a circle contains 360 degrees, that is 360°. The measurement of two points A and D of the circumference could have 15 portions of the circumference. In this case, this measurement would be represents as 15°.

The distance between two equidistant points A and B is a round shape geometrically defined as an arc. An angle is the ratio of the distance between two points A and B of the circumference divided by the radius R. This can be written as:

PI

The letter π, also written as PI, is a number used in various mathematical calculations. Its approximate value is 3.1415926535897932. The calculator of Microsoft Windows represents it as 3.1415926535897932384626433832795. To get the value of PI, Transact-SQL provides the PI() function. Its syntax is simply:

PI()

Radians

Arc

An angle is the ratio of an arc over the radius. Because an angle is a ratio and not a "physical" measurement, which means an angle is not a dimension, it is independent of the size of a circle. Obviously the angle represents the number of portions covered by three points. A better unit used to measure an angle is the radian or rad.

If you know the value of an angle in degrees and you want to get the radians, Transact-SQL provides the RADIANS() function. Its syntax is:

RADIANS(Expression)

This function takes as argument a value in degrees. If it succeeds in its calculation, it returns the radians value.

A cycle is a measurement of the rotation around the circle. Since the rotation is not necessarily complete, depending on the scenario, a measure is made based on the angle that was covered during the rotation. A cycle could cover part of the circle, in which case the rotation would not have been completed. A cycle could also cover the whole 360° of the circle and continue there after. A cycle is equivalent to the radian divided by 2 * Pi.

Degrees

If you know the radians but want to get the degrees of an angle, you can use the DEGREES() function. Its syntax is:

DEGREES(Expression)

This function takes as argument a value in radians. If it succeeds, it returns the equivalent value in degrees.

Trigonometric Functions

 

The Cosine of a Value

Trigonometry

Consider AB the length of A to B, also referred to as the hypotenuse. Also consider AC the length of A to C which is the side adjacent to point A. The cosine of the angle at point A is the ratio AC/AB. That is, the ratio of the adjacent length, AC, over the length of the hypotenuse, AB:

Cosine

The returned value, the ratio, is a double-precision number between -1 and 1.

To get the cosine of an angle, you can call the COS() function. Its syntax is:

COS(Expression)

The angle to be considered is passed as the argument to this function. The function then calculates and returns its cosine. Here is an example:

DECLARE @Angle As Decimal(6, 3);
SET @Angle = 270;
SELECT COS(@Angle) AS [Cosine of 270];
GO

This would produce:

Cosine

The Sine of a Value

Trigonometry

Consider AB the length of A to B, also called the hypotenuse to point A. Also consider CB the length of C to B, which is the opposite side to point A. The sine represents the ratio of CB/AB; that is, the ratio of the opposite side, CB over the hypotenuse AB.

To get the sine of an angle, you can use the SIN() function whose syntax is:

SIN(Expression)

The angle to be considered is passed as the argument. After its calculation, the function returns the sine of the angle between -1 and 1.

Here is an example:

DECLARE @Angle As Decimal(6, 3);
SET @Angle = 270;
SELECT SIN(@Angle) AS [Sine of 270];
GO

This would produce:

Sinus

The Tangent of a Value

Trigonometry

In geometry, consider AC the length of A to C. Also consider BC the length of B to C. The tangent is the result of BC/AC, that is, the ratio of BC over AC.

To get the tangent of an angle, you can use the TAN() function of Transact-SQL. Its syntax is:

TAN(Expression)

Here is an example:

DECLARE @Angle As Decimal(6, 3);
SET @Angle = 270;
SELECT TAN(@Angle) AS [Tangent of 270];
GO

This would produce:

Tangent

Practical LearningPractical Learning: Ending the Lesson

  1. Close the Query Editor
  2. If asked whether you want to save, click No
 
 
   
 

Previous Copyright © 2008-2013 FunctionX Next