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

SQL Expressions

String-Based Functions

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.

Practical Learning: Replacing Characters or Sub-Strings



In the Object Explorer, delete dbo.Last4DigitsOfSSN
Delete the code in the query window
To use the REPLACE() function, change the code as follows:
-- =============================================
-- 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


Execute the statement
Delete the code in the query window
To test the function, type the following
SELECT Exercise1.dbo.Last4DigitsOfSSN('244-04-8502');
GO


Execute the statement in the window
Arithmetic Functions


The Sign of a Number



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:




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