Search content within the blog

Wednesday, April 22, 2009

Built in functions in sqlserver

CAST
DECLARE @StrSalary Varchar(10),
@StrHours Varchar(6),
@WeeklySalary Decimal(6,2)
SET @StrSalary = '22.18';
SET @StrHours = '38.50';

SET @WeeklySalary = CAST(@StrSalary As Decimal(6,2)) *
CAST(@StrHours As Decimal(6,2));
SELECT @WeeklySalary;
GO

Convert
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 'Square Characteristics';
PRINT '-----------------------';
PRINT 'Side = ' + CONVERT(varchar(10), @Side, 10);
PRINT 'Perimeter = ' + CONVERT(varchar(10), @Perimeter, 10);
PRINT 'Area = ' + CONVERT(varchar(10), @Area, 10);
GO

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

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

Char
select char(65) As 'charA'


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


Upper
DECLARE @FIFA varchar(120)
SET @FIFA = 'this is a great color to choose'
SELECT @FIFA AS FIFA
SELECT UPPER(@FIFA) AS Converted

Left
CREATE FUNCTION GetUsername
(@FirstName varchar(40),
@LastName varchar(40))
RETURNS varchar(50)
AS
BEGIN
DECLARE @Username AS varchar(50);
SELECT @Username = LOWER(LEFT(@FirstName, 1)) +
LEFT(LOWER(@LastName), 4)
RETURN @Username;
END
GO

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

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

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

Please Click here to read further

No comments:

Post a Comment