Transact-SQL
Transact-SQL
CAST
String
Integer
CONVERT()
CAST(Expression AS DataType)
CAST()
DataType
Expression
Expression
4
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
CONVERT()
CAST()
CONVERT(DataType [ ( length ) ] , Expression [ , style ])
binary
Expression
(varchar, nvarchar, char, nchar)
CAST
-- 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;
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
String
SQL
Transact-SQL
1
SQL Server Management Studio
New Database...
2
Databases
3
Exercise1
C:\Microsoft SQL Server Database Development
Exercise1
Databases
Functions
4
OK
Programmability
Length
of String
LEN()
int LEN(String)
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
ASCII
ASCII
ASCII()
int ASCII(String)
ASCII
DECLARE @ES varchar(100)
SET @ES = 'El Salvador'
SELECT @ES AS ES
SELECT ASCII(@ES) AS [In ASCII Format]
American Standard Code fo r Informat ion Interchange
ASCII
1
ASCII
CHAR()
ASCII
ASCII
char CHAR(int value)
ASCII
CHAR
DECLARE @num int
SET @num = 69
SELECT CHAR(@num)
GO
E
Lowercase
LOWER()
varchar LOWER(String)
DECLARE @FIFA varchar(120)
SET @FIFA = 'Fédération Internationale de Football Association'
SELECT @FIFA AS FIFA
SELECT LOWER(@FIFA) AS Converted
New Query...
Exercise1
1
2
-- =============================================
-- 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
sql
3
F5
4
SELECT Exercise1.dbo.GetUsername('Francine', 'Moukoko');
GO
5
Programmability
6
Exercice1
Scalar-Valued
OK
Functions
Delete
Functions
dbo.GetUserName
Sub-Strings
LEFT()
varchar LEFT(String, NumberOfCharacters)
1
-- =============================================
-- Function: GetUsername
-- =============================================
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
F5
2
3
SELECT Exercise1.dbo.GetUsername('Francine', 'Moukoko');
GO
4
F5
Um
5
5
Sub-Strings
RIGHT()
varchar RIGHT(String, NumberOfCharacters)
1
-- =============================================
-- Function: Last4DigitsOfSSN
-- =============================================
CREATE FUNCTION Last4DigitsOfSSN(@SSN varchar(12))
RETURNS char(4)
AS
BEGIN
RETURN RIGHT(@SSN, 4);
END
GO
F5
2
3
SELECT Exercise1.dbo.Last4DigitsOfSSN('836483846');
GO
4
Sub-Strings
000
0000000000
0000 000 000
0000 000
REPLACE()
varchar REPLACE(String, FindString, ReplaceWith)
binary REPLACE(String, FindString, ReplaceWith)
FindString
ReplaceWith
1
-- =============================================
-- 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
2
F5
3
SELECT Exercise1.dbo.Last4DigitsOfSSN('244-04-8502');
GO
4
1
SIGN()
1
SIGN(Expression)
DECLARE @Number As int;
SET @Number = -57.05;
SELECT SIGN(@Number) AS [Sign of -57.05];
GO
0
IF
-- 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
ABS()
ABS(Expression)
DECLARE @NumberOfStudents INTEGER;
SET @NumberOfStudents = -32;
SELECT ABS(@NumberOfStudents) AS [Number of Students];
GO
13
12
25
12.155
24
24.06
24
13
12.155
24
24.06
CEILING()
CEILING(Expression)
CEILING
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
DECLARE @Number1 As Numeric(6, 2),
@Number2 As Numeric(6, 2)
SET @Number1 = 12.155;
SET @Number2 = -24.06;
PRINT 'The ceiling of 12.155 is ' +
CONVERT(varchar(10), CEILING(@Number1));
PRINT 'The ceiling of –24.06 is ' +
CONVERT(varchar(10), CEILING(@Number2));
GO
FLOOR()
FLOOR(Expression)
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
ex
EXP()
EXP(Expression)
ex
DECLARE @Number As Numeric(6, 2);
SET @Number = 6.48;
SELECT EXP(@Number) AS [Exponent of 6.48];
GO
x
xy
y
xy
x
POWER()
POWER(x, y)
POWER
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
LOG
LOG()
LOG(Expression)
LOG
DECLARE @Number As Decimal(6, 2);
SET @Number = 48.16;
SELECT LOG(@Number) AS [Natural Logarithm of 48.16];
GO
Ln
e ≈ 2.17828
e
LOG
2
10
LOG10()
10
LOG10(Expression)
10
DECLARE @Number As Decimal(6, 2);
SET @Number = 48.16;
SELECT LOG10(@Number) AS [Base-10 Logarithm of 48.16];
GO
x = 10y y = log10x
Log
3
SQRT()
SQRT(Expression)
SQRT
DECLARE @Number As Decimal(6, 2);
SET @Number = 48.16;
SELECT SQRT(@Number) AS [The square root of 48.16 is];
GO
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
C
R
C
360
°
R
B
A
θ
Pi
п
Transact-SQL
п
3.1415926535897932
PI()
RADIANS()
RADIANS(Expression)
DEGREES()
DEGREES(Expression)
Cosine
adjacent
AC
AC/AB
ABC
hypotenuse
Opposite
1 1
AB
BC
θ
Cos
COS()
COS(Expression)
COS
DECLARE @Angle As Decimal(6, 3);
SET @Angle = 270;
SELECT COS(@Angle) AS [Cosine of 270];
GO
θ
(Sine)
CB/AB
SIN()
SIN(Expression)
1
1
DECLARE @Angle As Decimal(6, 3);
SET @Angle = 270;
SELECT SIN(@Angle) AS [Sine of 270];
GO
SIN
Tangent
TAN()
TAN(Expression)
DECLARE @Angle As Decimal(6, 3);
SET @Angle = 270;
SELECT TAN(@Angle) AS [Tangent of 270];
GO
BC/AC
Transact-SQL
SmallDateTime
DateTime
GETDATE()
GETDATE()
DATEADD
DATEADD(TypeOfValue, ValueToAdd, DateOrTimeReferenced)
DateOrTimeReferenced
ValueToAdd
TypeOfValue
TypeOfValue
Year
yyyy
yy
DECLARE @Anniversary As DateTime;
SET @Anniversary = '2002/10/02';
SELECT DATEADD(yy, 4, @Anniversary) AS Anniversary;
GO
Quarter
qq
d
TypeOfValue
DECLARE @NextVacation As DateTime;
SET @NextVacation = '2002/10/02';
SELECT DATEADD(Quarter, 2, @NextVacation) AS [Next Vacation];
GO
m
TypeOfValue
5
Month
DECLARE @SchoolStart As DateTime;
SET @SchoolStart = '2004/05/12';
SELECT DATEADD(m, 5, @SchoolStart) AS [School Start];
GO
mm
النتيجة
االختصار
yy
yyyy
q
qq
m
mm
y
dy
d
dd
wk
ww
hh
n
mi
s
ss
ms
نوع القيمة
Year
quarter
Month
dayofyear
Day
Week
Hour
minute
second
millisecond
DATEDIFF
Transact-SQL
DATEDIFF(TypeOfValue, StartDate, EndDate)
DATEADD
DECLARE @DateHired As DateTime,
@CurrentDate As DateTime;
SET @DateHired = '1996/10/04';
SET @CurrentDate = GETDATE();
SELECT DATEDIFF(year, @DateHired, @CurrentDate)
AS [Current Experience];
GO
1
Delete
Exercise1
Databases
OK
2
3
1
40
40
40
2
3
4