Microsoft Visual C# & ADO.NET: Introduction to Functions

	Introduction to Functions

Functions Fundamentals

Introduction

A function is like a method in C#, except that a function does not belong to a class. As seen in C#, a function is a relatively small task that should be performed aside but can be accessed any time to give a result. In Transact-SQL, a function is considered an object. Based on this, you must create a function and execute it before using it. The function then becomes part of a database.

In Transact-SQL, the primary formula of creating a function is:

CREATE FUNCTION FunctionName()

The Name of a Function

In SQL, a function was created as an object. As such, it must have a name. In our lessons, here are the rules we will use to name our functions:

The name of a function will resemble an action. An example is calculate
A name will start with either an underscore or a letter. Examples are _n, act, or Perform
After the first character as an underscore or a letter, the name will have combinations of underscores, letters, and digits. Examples are _n24 or act_52_t
A name will not include special characters such as !, @, #, $, %, ^, &, or *
We will avoid using spaces in a name
If the name is a combination of words, each word will start in uppercase. Examples are DoSomething, _CreateStudentsRecords, Get_Age, or _Calculate_Volume_Area

Returning a Value From a Function

Like a method in C#, a function can return or produce a result. When creating a function, you must specify the type of value it must return. To provide this information, after the name of the function, type the RETURNS keyword followed by a definition for a data type. Here is a simple example:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)

After specifying the type of value the function would return, you can create a body for the function. While in C# the body of a function starts with {, in SQL, the body starts with the BEGIN keyword. The body of a function closes with the END keyword, equivalent to } in C#. Here is an example:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
BEGIN

END

Optionally, you can type the AS keyword before the BEGIN keyword:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
AS
BEGIN

END

Between the BEGIN and END keywords, you can define the assignment the function must perform. After performing this assignment, just before the END keyword, you must specify the value that the function returns. This is done by typing the RETURN keyword followed by an expression. A sample formula is:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
AS
BEGIN
    RETURN Expression
END

Here is an example

CREATE FUNCTION GetFullName()
RETURNS varchar(100)
AS
BEGIN
	RETURN 'Doe, John'
END

Function Calling

After a function has been created, you can use the value it returns. Like a method in C#, using a function is also referred to as calling it. To call a function, you must qualify its name. To do this, type the name of the database in which it was created, followed by the period operator, followed by dbo, followed by the period operator, followed by the name of the function, and its parentheses. The formula to use is:

DatabaseName.dbo.FunctionName()

Because a function returns a value, you can use that value as you see fit. For example, you can use SELECT to display the function's value in a query window. Here is an example that calls a function:

private void btnEvaluate_Click(object sender, EventArgs e)
{
    string strConnection =
	"Data Source=(local);" +
	"Database='UtilityCompany1';" +
	"Integrated Security=yes;";
    string ExecuteFunction = "SELECT dbo.EvaluateInvoice();";

    using (SqlConnection connection = new SqlConnection(strConnection))
    {
	SqlCommand command =
	    new SqlCommand(ExecuteFunction, connection);

	connection.Open();
    	SqlDataReader rdr = command.ExecuteReader();

    	while (rdr.Read())
    	{
	    txtAmountDue.Text = rdr[0].ToString();
        }

        rdr.Close();
    }
}

Function Maintenance

Renaming a Function

Because a function in Transact-SQL is treated as an object, it may need maintenance. Some of the actions you would take include renaming, modifying, or deleting a function.

To rename a function, in the Object Explorer in Microsoft SQL Server, right-click it and click Rename. Type the desired new name and press Enter.

Deleting a Function

If you create a function and decide that you don't need it any more, you can delete it. To programmatically delete a function, type DROP FUNCTION followed by the name of the function and execute the statement. Here is an example:

private void btnDatabase_Click(object sender, EventArgs e)
{
    string strConnection =
		"Data Source=(local);" +
		"Database='UtilityCompany1';" +
		"Integrated Security=yes;";
    string CreateFunction = "DROP FUNCTION EvaluateInvoice;";

    using (SqlConnection connection = new SqlConnection(strConnection))
    {
	SqlCommand command =
		new SqlCommand(CreateFunction, connection);

    	connection.Open();
	command.ExecuteNonQuery();

  	MessageBox.Show("A function named \"EvaluateInvoice\" has been deleted.");
    }
}

Modifying a Function

As mentioned already, in the body of the function, you define what the function is supposed to take care of. As a minimum, a function can return a simple number, typed on the right side of the RETURN keyword. Here is an example:

CREATE FUNCTION Addition()
RETURNS int
BEGIN
    RETURN 1
END

You can also declare new variables in the body of the function to help in carrying the assignment. A variable declared in the body of a function is referred to as a local variable. Once such a variable has been declared, it can be used like any other variable. Here is an example:

CREATE FUNCTION Addition()
RETURNS int
BEGIN
    DECLARE @Number1 int
    SET @Number1 = 588
    RETURN @Number1 + 1450
END

Function Arguments

Introduction

Like a method in C#, a function can be passed some arguments. Put it another way, when you create a function, instead of, or in addition to, local variables, you may want the code that will call the function to provide the values needed to perform the assignment. An external value that is provided to a function is called a parameter. A function can also take more than one parameter.

We know that a function's name is also followed by parentheses. If the function doesn't use any argument, its parentheses are left empty. If a function takes an argument, when you create the function, you must specify a name and the type of value of the parameters. The name of the parameter is created with the @ sign, like a variable as we saw in the previous lesson. Here is an example:

CREATE FUNCTION Addition(@Number1 Decimal(6,2))

When a function takes a parameter, in the body of the function, you can use the parameter as if you knew its value, as long as you respect the type of that value. Here is an example:

CREATE FUNCTION Addition(@Number1 Decimal(6,2))
RETURNS Decimal(6,2)
BEGIN
    RETURN @Number1 + 1450
END

When you call a function that takes one parameter, you must supply a value for that argument. To do this, type the value of the parameter in the parentheses of the function.

A Function With Various Arguments

Instead of only one parameter, you can also create a function that takes more than one parameter. In this case, separate the arguments in the parentheses of the function with a comma. Here is an example:

CREATE FUNCTION Addition(@Number1 Decimal(6,2), @Number2 Decimal(6,2))

Once again, in the body of the function, you can use the parameters as if you already knew their value. You can also declare local variables and involve them with parameters as you see fit. Here is an example:

CREATE FUNCTION Addition(@Number1 Decimal(6,2),
			 @Number2 Decimal(6,2))
RETURNS Decimal(6,2)
BEGIN
    DECLARE @Result Decimal(6,2)
    SET @Result = @Number1 + @Number2
    RETURN @Result
END;
GO

When calling a function that takes more than one parameter, in the parentheses of the function, provide a value for each parameter, in the exact order they appear in the parentheses of the function. Here is an example:

SELECT Variables1.dbo.Addition(1450, 228);

You can also pass the names of already declared and initialized variables. Here is an example that calls the above function:

DECLARE @Nbr1 Decimal(6,2),
        @Nbr2 Decimal(6,2)
SET @Nbr1 = 4268.55
SET @Nbr2 =26.83
SELECT @Nbr1 As First,
       @Nbr2 As Second,
       Variables1.dbo.Addition(@Nbr1, @Nbr2) AS Result

Introduction to Built-Functions

Overview

One way you can assist users is to use functions that perform otherwise complex tasks. To assist you, Transact-SQL provides a vast collection of read-made functions. If you use one of those functions, they must be transmitted to the SQL interpreter. In some if not most cases, you can use classes of the .NET Framework.

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

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.

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.

In a C# application, to convert a value, you can use either the Convert class or a Parse() method. In Transact-SQL, to convert a value, 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 23.

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.

Here is an example:

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

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 ])

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.

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

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)

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

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]

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

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

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

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
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
```
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
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)
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
```
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

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

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

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

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

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 nvarchar(100)
SET @ES = N'El Salvador'
SELECT @ES AS ES
SELECT ASCII(@ES) AS [In ASCII Format]

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 nvarchar(120)
SET @FIFA = N'F�d�ration Internationale de Football Association'
SELECT @FIFA AS FIFA
SELECT LOWER(@FIFA) AS Converted

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.

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.

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

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.


Previous	Copyright © 2007-2022, FunctionX, Inc.	Next