Microsoft SharePoint Team Blog
The official blog of the Microsoft SharePoint Product Group

SQL Expressions

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.

Practical Learning: Introducing Transact-SQL Functions



Start Microsoft SQL Server and connect to the server
Right-click the Databases node and click New Database...
Set the Name to Exercise1
Set the Paths to C:\Microsoft SQL Server Database Development
Click OK
Expand the Databases node
Expand Exercise1
Expand Programmability
Expand Functions
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:

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


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:

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

Practical Learning: Converting a String to Lowercase



In the Object Explorer, right-click Exercise1 and click New Query...
To create a function, type the following:
-- =============================================
-- 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


Press F5 to execute the statement
Save the code as Exercise
Press Ctrl + A to select the code and press Delete to remove the code
To test the function, type the following:
SELECT Exercise1.dbo.GetUsername('Francine', 'Moukoko');
GO


Press F5 to execute the statement

In the Object Explorer, expand the Databases node if necessary, and expand Exercise1
Expand Programmability
Expand Functions
Expand Scalar-Valued Functions
Right-click dbo.GetUserName and click Delete
In the Delete Object dialog box, click OK
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.





source : http://blogs.msdn.com/sharepoint/default.aspx