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. 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.

1. All Computers: Start the computer and log in using your domain account
2. Server: Launch Microsoft SQL Server. In the Server Name combo box, make sure the name of the computer is selected. In the Authentication combo box, make sure Windows Authentication is selected. Make sure the account you are using is selected in the User Name combo box. Click Connect
3. Each Student:
   1. Start Microsoft SQL Server
   2. In the Server Name combo box, select the name of the server or type it
   3. In the Authentication combo box, select SQL Server Authentication
   4. In the User Name combo box, type the name of the domain, followed by \, and followed by the login name you were given
   5. In the Password combo box, type your password
   6. Click Connect
4. All Computers: On the Standard toolbar, click the New Query button
5. Server: To create a new database, type the following
   

   CREATE DATABASE Exercise1;
   GO
   
   USE Exercise1;
   GO

6. Server: Press F5 to execute
7. Each Student: Type the following:

   USE Exercise1;
   GO

8. Each Student: Press F5 to execute

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 with conversion, you can use either the CAST() or the CONVERT() function. The  syntax of the CAST() function is:

CAST(Expression AS DataType)

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

From here to the end of this lesson, all instructions are intended for All Computers:

1. Click the top section of the Query window and press Ctrl + A to select everything
2. Type the following:
   

   DECLARE @StrSalary Varchar(10),
   	@StrHours  Varchar(6),
   	@WeeklySalary Decimal(6,2)
   SET @StrSalary = N'22.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

Like CAST(), the CONVERT() function is used to convert a value. Unlike CAST(), CONVERT can be used to convert a value 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 ]) The first argument must be a known data type, such as those we reviewed in Lesson 6. 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 the data type's own parentheses. As reviewed for the CAST() function, the Expression is the value that needs to be converted.

1. Click the top section of the Query window and press Ctrl + A to select everything
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 'Square Characteristics';
   PRINT '-----------------------';
   PRINT 'Side      = N' + CONVERT(varchar(10), @Side, 10);
   PRINT 'Perimeter = N' + CONVERT(varchar(10), @Perimeter, 10);
   PRINT 'Area      = N' + CONVERT(varchar(10), @Area, 10);
   GO

3. Press F5 to execute

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.

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) 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, in Lesson 5, we mentioned a system database names 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. The formula you would use is: IF EXISTS ( SELECT name FROM sys.databases WHERE name = N'DatabaseName' )

In the DatabaseName placeholder, you can enter the name of the database.

The string is the most basic, 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 can divide in categories that include character-based, conversions, addition, sub-strings, etc.

Some operations performed on strings require that you know the number of characters of a string. This is because some operations require a minimum number of characters and some other functions require that the string have at least one character. The number of characters of a string is also called the length of the string.

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.

1. Click the top section of the Query window and press Ctrl + A to select everything
2. Type the following:
   

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

3. Press F5 to execute

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 use the ASCII() function. Its syntax is:

int ASCII(String)

This function takes as argument as string and returns the ASCII code of the first (the left) character of the string.

1. Click the top section of the Query window and press Ctrl + A to select everything
2. Type the following:
   

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

3. Press F5 to execute

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

char CHAR(int value)

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

As you may know already, a string can be made of uppercase, lowercase, 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 use the LOWER() function. Its syntax is:

varchar LOWER(String)

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:

1. Click the top section of the Query window and press Ctrl + A to select everything
2. To create a function, type the following:
   

   USE Exercise1;
   GO
   -- =============================================
   -- Function: GetUsername
   -- =============================================
   
   CREATE FUNCTION GetUsername
   	(@FirstName varchar(40), 
   	 @LastName varchar(40))
   RETURNS varchar(50)
   AS
   BEGIN
   	DECLARE @Username AS varchar(50);
   	SELECT @Username = LOWER(@FirstName) + LOWER(@LastName);
   	RETURN @Username;
   END
   GO

3. Press F5 to execute the statement
4. Press Ctrl + A to select the code and press Delete to remove the code
5. To test the function, type the following:
   

   SELECT Exercise1.dbo.GetUsername(N'Francine', N'Moukoko');
   GO

6. Press F5 to execute the statement
    
   
7. In the Object Explorer, expand the Databases node if necessary, and expand Exercise1
8. Expand Programmability
9. Expand Functions
10. Expand Scalar-Valued Functions
11. Right-click dbo.GetUserName and click Delete
12. In the Delete Object dialog box, click OK

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: varchar LEFT(String, NumberOfCharacters) 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.

1. Click the top section of the Query window and press Ctrl + A to select everything
2. To use the LEFT() function, type the following:
   

   USE Exercise1;
   GO
   -- =============================================
   -- Function: GetUsername
   -- =============================================
   
   CREATE FUNCTION GetUsername
   	(@FirstName varchar(40), 
   	 @LastName varchar(40))
   RETURNS varchar(50)
   AS
   BEGIN
   	DECLARE @Username AS varchar(50);
   	SELECT @Username = LOWER(LEFT(@FirstName, 1)) + 
   			   LEFT(LOWER(@LastName), 4)
   	RETURN @Username;
   END
   GO

3. Press F5 to execute the statement
4. Click the top section of the Query window and press Ctrl + A to select everything
5. To test the function, type the following:
   

   SELECT Exercise1.dbo.GetUsername(N'Francine', N'Moukoko');
   GO

6. Execute the statement in the window
    
   
7. Change the call with a last name shorter than 5 characters such as "Um" and execute the statement. Here is an example:
    
   

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:

varchar RIGHT(String, NumberOfCharacters)

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.

1. Click the top section of the Query window and press Ctrl + A to select everything
2. To create a function, type the following:
   

   USE Exercise1;
   GO
   -- =============================================
   -- Function: Last4DigitsOfSSN
   -- =============================================
   
   CREATE FUNCTION Last4DigitsOfSSN(@SSN varchar(12))
   RETURNS char(4)
   AS
   BEGIN
   	RETURN RIGHT(@SSN, 4);
   END
   GO

3. Press F5 to execute the statement
4. Click the top section of the Query window and press Ctrl + A to select everything
5. To test the function, type the following:
   

   USE Exercise1;
   GO
   SELECT Exercise1.dbo.Last4DigitsOfSSN(N'836483846');
   GO

6. Execute the statement
    
   

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. 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. 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 other(s). To replace one character or a sub-string from a string, you can use the REPLACE() function. Its syntax is: varchar REPLACE(String, FindString, ReplaceWith)

or

binary REPLACE(String, FindString, ReplaceWith)

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.

1. In the Object Explorer, delete dbo.Last4DigitsOfSSN
2. Click the top section of the Query window and press Ctrl + A
3. To use the REPLACE() function, change the code as follows:
   

   USE Exercise1;
   GO
   
   -- =============================================
   -- Function: Last4DigitsOfSSN
   -- =============================================
   
   CREATE FUNCTION Last4DigitsOfSSN(@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

4. Execute the statement
5. Click the top section of the Query window and press Ctrl + A
6. To test the function, type the following
   

   USE Exercise1;
   GO
   
   SELECT Exercise1.dbo.Last4DigitsOfSSN(N'244-04-8502');
   GO

7. Execute the statement in the window

In arithmetic, a number is considered as being negative (less than 0), null (equal to 0), or positive (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, null, 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

• If the Expression is null, the function returns 0
   

  DECLARE @Number As int; SET @Number = 0; SELECT SIGN(@Number) AS [Sign of Number]; GO

• 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

Based on this, you can use 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. Here is an example:

-- 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;
IF SIGN(@Side) > 0
    BEGIN
	PRINT 'Square Characteristics';
	PRINT '-----------------------';
	PRINT 'Side      = N' + CONVERT(varchar(10), @Side, 10);
	PRINT 'Perimeter = N' + CONVERT(varchar(10), @Perimeter, 10);
	PRINT 'Area      = N' + CONVERT(varchar(10), @Area, 10);
    END;
ELSE
	PRINT 'You must provide a positive value';
GO

Here is an example of executing the statement:

Here is another example of executing the statement:

Notice that the interpreter acts differently in response to the result of the SIGN() function.

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)

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:

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:

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 'The ceiling of 12.155 is ' + 
      CONVERT(varchar(10), CEILING(@Number1));
PRINT 'The ceiling of �24.06 is ' +
      CONVERT(varchar(10), CEILING(@Number2));
GO

This would produce:

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: