String-Based Functions

Introduction

 

The Length of a String

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. Here is an example:

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Dim Connect As SqlConnection = _
            New SqlConnection("Data Source=(local);" & _
                              "Database='Exercise';" & _
                              "Integrated Security=yes;")
        Dim command As SqlCommand = _
  		New SqlCommand("DECLARE @FIFA varchar(120) " & _
  		"SET @FIFA = 'Fédération Internationale de Football Association' " & _
          			"SELECT @FIFA, LEN(@FIFA);", _
          			Connect)
        Connect.Open()
        Dim rdr As SqlDataReader = Command.ExecuteReader()

        While rdr.Read()
            MsgBox("" & rdr(0) & " has " & rdr(1) & " characters.")
        End While

        rdr.Close()
        Connect.Close()
    End Sub
End Class

This would produce:

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. Here is an example:

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

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 do not 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:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Dim Connect As SqlConnection = _
            New SqlConnection("Data Source=(local);" & _
                              "Database='Exercise';" & _
                              "Integrated Security=yes;")
    Dim command As SqlCommand = _
	    New SqlCommand("DECLARE @FIFA varchar(120) " & _
 	"SET @FIFA = 'Fédération Internationale de Football Association' " & _
        		   "SELECT @FIFA, LOWER(@FIFA);", _
    			   Connect)
    Connect.Open()
    Dim rdr As SqlDataReader = Command.ExecuteReader()

    While rdr.Read()
        MsgBox("Original: " & rdr(0) & _
              vbCrLf & "Converted: " & rdr(1))
    End While

    rdr.Close()
    Connect.Close()
End Sub

This would produce:

Remember that the LOWER() function returns a string. Therefore, you can add its result to another call of the function using the + operator. Here is an example:

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

You can then call the function as follows:

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

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.

Here is an example:

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub btnCreateUsername_Click(ByVal sender As System.Object, _
                                        ByVal e As System.EventArgs) _
                                        Handles btnCreateUsername.Click
        Dim FirstName As String, LastName As String

        FirstName = txtFirstName.Text
        LastName = txtLastName.Text

        Dim Connect As SqlConnection = _
  		New SqlConnection("Data Source=(local);" & _
      				  "Database='Exercise1';" & _
      				  "Integrated Security=yes;")
        Dim Cmd As SqlCommand = _
     	New SqlCommand("DECLARE @FirstName nvarchar(40), " & _
        	       "        @LastName nvarchar(40), " & _
          	       "        @Username AS nvarchar(50); " & _
             	       "SET @FirstName = '" & FirstName & "'; " & _
             	       "SET @LastName = '" & LastName & "'; " & _
             	       "SET @Username = LOWER(LEFT(@FirstName, 1)) + " & _
             	       "	LEFT(LOWER(@LastName), 4) " & _
             	       "SELECT @Username;", _
        Connect)
        connect.Open()
        Dim rdr As SqlDataReader = Cmd.ExecuteReader()

        While rdr.Read()
            txtUsername.Text = rdr(0)
        End While

        rdr.Close()
        connect.Close()
    End Sub

    Private Sub btnClose_Click(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles btnClose.Click
        End
    End Sub
End Class

Here is an example of running the program:

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.

Here is an example:

-- =============================================
-- Function: Last4DigitsOfSSN
-- =============================================

CREATE FUNCTION Last4DigitsOfSSN(@SSN varchar(12))
RETURNS char(4)
AS
BEGIN
	RETURN RIGHT(@SSN, 4);
END
GO

You can call the above function as follows:

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

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.

Here is an example:

-- =============================================
-- 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, ' ', '');
	-- Now remove the dashes "-" if they exist
	SET @StringWithoutSymbol = REPLACE(@StringWithoutSymbol, '-', '');
	RETURN RIGHT(@StringWithoutSymbol, 4);
END
GO

You can call the above function as follows:

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

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

Here is an example of executing the statement:

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

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

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

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

 

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:

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 

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 

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:

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

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

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

IF SIGN(@Number) > 0
    PRINT 'The square root of 258.4062 is ' + 
           CONVERT(varchar(12), SQRT(@Number));
ELSE
    PRINT 'You must provide a positive number';
GO

Here is one example of executing the statement:

Date and time values are highly used in database applications. They involve sales, time sheets, taxes, overtime work, etc. Based on this usefulness, their operations are supported by various libraries you will be using when developing your application. Without being the most elaborate on this issue, Transact-SQL provides its own level of support for date and time values.

Before using a date or a time value in a calculation, remember that you must first get it one way or another. You can define a date or a time constant in your application. An example would be '1992/10/28'. You can declare a DateTime or a SmallDateTime variable and initialize it as you see fit. You may get a date or a time from another function. As the last alternative, you may get a date or time from another application or from a user. Once you have an appropriate date, you can use it.

One of the ways you can assist the user with date and time is to get the current date or the current time. For example, if you create a time sheet, when the user starts using it, it would be convenient to fill part of the time sheet with such predictable values.

To get the current date and the current time of the computer that a user is using, you can use the GETDATE() function of Transact-SQL. Its syntax is:

GETDATE()

This function simply returns the current date and time of the operating system.

One of the primary operations you may want to perform on a date or a time value would consist of adding a value to it. To support this operation, Transact-SQL provides the DATEADD() function. Its syntax is:

DATEADD(TypeOfValue, ValueToAdd, DateOrTimeReferenced)

The third argument to this function is the value of a date or a time on which the operation will be performed. It can be a constant value in the form of 'year/month/day' for a date or 'hour:minutes AM/PM' for a time.

The second argument is the value that will be added. It should be a constant integer, such as 8, or a floating point value, such as 4.06.

When calling this function, you must first specify the type of value that you want to add. This type is passed as the first argument. It is used as follows:

• If you want to add a number of years to a date, specify the TypeOfValue as Year or yy, or yyyy (remember that SQL is case-insensitive). Here is an example:
   

  DECLARE @Anniversary As DateTime; SET @Anniversary = '2002/10/02'; SELECT DATEADD(yy, 4, @Anniversary) AS Anniversary; GO

• If you want to add a number of quarters of a year to a date, specify the TypeOfValue as Quarter or d, or qq. Here is an example:
   

  DECLARE @NextVacation As DateTime; SET @NextVacation = '2002/10/02'; SELECT DATEADD(Quarter, 2, @NextVacation) AS [Next Vacation]; GO

• If you want to add a number of months to a date, specify the TypeOfValue as Month or m, or mm. The following example adds 5 months to its date:
   

  DECLARE @SchoolStart As DateTime; SET @SchoolStart = '2004/05/12'; SELECT DATEADD(m, 5, @SchoolStart) AS [School Start]; GO

In the same way, you can add values as follows:

Another regular operation performed on a date or a time value consists of getting the number of units that has elapsed in the range of two dates or two time values. To support this operation, Transact-SQL provides the DATEDIFF() function. Its syntax is:

DATEDIFF(TypeOfValue, StartDate, EndDate)

This function takes three arguments. The second argument is the starting date or the starting time of the range to be considered. The third argument is the end or last date or time of the considered range. You use the first argument to specify the type of value you want the function to produce. This argument uses the same value as those of the DATEADD() function:

Here is an example that calculates the number of years that an employees has been with the company:

DECLARE @DateHired As DateTime,;
	@CurrentDate As DateTime;
SET @DateHired = '1996/10/04';
SET @CurrentDate  = GETDATE();
SELECT DATEDIFF(year, @DateHired, @CurrentDate)
       AS [Current Experience];
GO

This would produce:

1. Write a function that can calculate the yearly salary of a job if an applicant is given the hourly salary. Consider that the yearly salary is based on 40 hours a week. In your function, if the hourly salary is given as a negative value, do not perform the calculation
2. Write a function that can calculate the area of a circle if given the radius
3. Write a procedure (a function) that processes a cylinder. It calculates and displays the diameter of the base, the circumference of the base, the base area, the side area, the total area, and the volume, given the radius and the height of a cylinder