Home

Transact-SQL Built-In Functions

 

String-Based Functions

 

Introduction

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.

 

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:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Exercise6
{
    public partial class Exercise : Form
    {
	public Exercise()
	{
	    InitializeComponent();
	}

	private void btnDatabase_Click(object sender, EventArgs e)
	{
	    SqlConnection connection =
		new SqlConnection("Data Source=(local);" +
				  "Database='Exercise1';" +
				  "Integrated Security=yes;");
	    SqlCommand command =
		new SqlCommand("DECLARE @FIFA varchar(120) " +
		"SET @FIFA = 'Fédération Internationale de Football Association' " +
			       "SELECT @FIFA, LEN(@FIFA);",
			       connection);
	    connection.Open();
	    SqlDataReader rdr = command.ExecuteReader();

	    while (rdr.Read())
	    {
		MessageBox.Show("\"" + rdr[0].ToString() +
			"\" has " + rdr[1].ToString() + " characters.");
	    }

	    rdr.Close();
	    connection.Close();
	}
    }
}

This would produce:

String Length

 

String Conversions: 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 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]

String Conversions: Converting From ASCII to Integer

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.

String Conversions: Lowercase

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:

private void btnDatabase_Click(object sender, EventArgs e)
{
    SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=yes;");
    SqlCommand command =
	new SqlCommand("DECLARE @FIFA varchar(120) " +
	"SET @FIFA = 'Fédération Internationale de Football Association' " +
		      "SELECT @FIFA, LOWER(@FIFA);",
    connection);
    connection.Open();
    SqlDataReader rdr = command.ExecuteReader();

    while (rdr.Read())
    {
	MessageBox.Show("Original: " + rdr[0].ToString() +
			"\nConverted: " + rdr[1].ToString());
    }

    rdr.Close();
    connection.Close();
}

This would produce:

String Conversion to Lowercase

 

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

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:

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:

The Starting Characters of a String 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Exercise6
{
    public partial class Exercise : Form
    {
	public Exercise()
	{
	    InitializeComponent();
	}

	private void btnCreateUsername_Click(object sender, EventArgs e)
	{
	    string FirstName, LastName;

	    FirstName = txtFirstName.Text;
	    LastName = txtLastName.Text;

	    SqlConnection connection =
		new SqlConnection("Data Source=(local);" +
				  "Database='Exercise1';" +
				  "Integrated Security=yes;");
	    SqlCommand command =
		new SqlCommand("DECLARE @FirstName varchar(40), " +
			       "        @LastName varchar(40), " +
			       "        @Username AS varchar(50); " +
			       "SET @FirstName = '" + FirstName + "'; " +
			       "SET @LastName = '" + LastName + "'; " +
			       "SET @Username = LOWER(LEFT(@FirstName, 1)) + " +
			       "LEFT(LOWER(@LastName), 4) " +
			       "SELECT @Username;",
	    connection);
	    connection.Open();
	    SqlDataReader rdr = command.ExecuteReader();

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

    	    rdr.Close();
	    connection.Close();
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
	    Close();
    	}
    }
}

Here is an example of running the program:

Sub-Strings: 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:

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

RIGHT

Sub-Strings: 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. 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
 

Published on Monday 24 December 2007

 

Home Copyright © 2007 FunctionX, Inc.