Introduction to Built-In Functions
Introduction to Built-In Functions
Fundamentals of Built-In Functions
Introduction
To assist you with various types of operations, Transact-SQL ships with many already created and tested functions. These are referred to as built-in functions. To help you identify the functions you can use, they are categorized by their types and probably their usefulness. Because of their complexities, some values can be easily recognized or fixed. Such a type of value is referred to as deterministic because it is always the same. There are other values that cannot be known in advance because they change based on some circumstances. Such a value is referred to as non-deterministic. To support determinism and non-determinism, Transact-SQL provides two broad categories of functions. A function that always returns the same or known value is referred to as deterministic. A function whose returned value may depend on a condition is referred to as non-deterministic. |
 |
Parsing an Expression or Value
Parsing consists of scanning, that is, checking every part (such as every symbol) of an expression or a word to match a pattern. An example of parsing is to check every symbol in a combination to find out if it is a number or something else. Based on this, parsing something like 275.25 would determine whether this is a decimal number or not.
To assist you with parsing, Transact-SQL provides a function named PARSE. Its syntax is:
PARSE ( string_value AS data_type [ USING culture ] );
This function takes one argument, passed as a string and accompanied by its data type preceded by the AS keyword. The function is used to "scan" an expression that is passed as the string_value argument. The expression must follow the rules of the data_type argument. For example, suppose you want to find out if some value is an integer. You can pass the data_type as int. Then, the expression passed as the argument must include digits and no other character or symbol.
If the argument may include international characters or formats (Unicode), you should indicate the language, called a culture, that the argument follows.
If the PARSE() function is not able to determine the type or if the value of the argument doesn't follow the rule of the data_type, this function produces (throws) an error. As an alternative to PARSE(), Transact-SQL provides the TRY_PARSE() function. Its syntax is:
TRY_PARSE ( string_value AS data_type [ USING culture ] )
As you can see, this function uses the same argument as PARSE. The difference is that, while PARSE() produces an error if the parsing operation fails, TRY_PARSE produces NULL (if the parsing operation fails). This means that, in most cases, you should prefer TRY_PARSE() instead of PARSE().
A value is primarily considered a string. If a value is something other than a string, before using such a value, you should first convert it to the appropriate type, that is, from a string to the expected type.
To assist you to convert a value, Transact-SQL provides a function named CAST. Its syntax is:
CAST ( expression AS data-type [ ( length ) ] );
The expression is the value that needs to be cast. data-type is the type of value into which you want to convert the expression. The data-type can be one of those we have used so far.
If the CAST() function is not able to cast the expression (if it fails), it produces (throws) an error. As an alternative to CAST(), Transact-SQL provides a function named TRY_CAST. Its syntax is:
TRY_CAST ( expression AS data_type [ ( length ) ] )
This function takes the same arguments as CAST. The difference is that, instead of producing an error, if TRY_CAST() fails, it returns NULL. This also means that, in most cases, you should use TRY_CAST() instead of CAST.
To provide you with a formal way to convert a value, Transact-SQL has a function named CONVERT. The syntax of the CONVERT() function is:
CONVERT(data-type [ ( length ) ] , expression [ , style ])
Unlike CAST(), the CONVERT() function can be used to convert a value from its original type into a non-similar type. For example, you can use CONVERT to cast a number into a string and vice-versa.
 |
The first argument must be a known data type, such as those we reviewed already. If you are converting the value into a string (varchar, nvarchar, char, nchar) or a binary type, you should specify the number of allowed characters in the data type's own parentheses, as the length argument. As reviewed for the CAST() function, the expression is the value that needs to be converted. If the conversion is performed on a date or time value, the style argument is a number that indicates how that conversion must proceed. If the CONVERT() function is not able to perform its operation, it produces an error. |
Because of some difficulties that can result from conversion, Transact-SQL provides a function named TRY_CONVERT. Its syntax is:
TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
The arguments are the same used in the CONVERT() function. The difference is that if the TRY_CONVERT() function fails, it returns NULL instead of producing (throwing) an error.
Transact-SQL Macros and Metadata Functions
Introduction to Macros
A macro is an action that can be performed on a database or certain parts of a database or of a database server. A macro resembles a function without being exactly one. Transact-SQL provides various macros to assist you with managing databases and their objects.
Checking the Existence of Something
One of the common operations you perform in Microsoft SQL Server is to check whether something (almost anything) exists. To assist you with this, Transact-SQL provides a macro named EXISTS. Its syntax is: BIT EXISTS(SELECT Something) This macro takes one argument. The argument must be a SELECT statement that would be used to get the value whose existence would be checked. For example, we know a system database named databases that contains a record of all databases stored on your server. You can use the EXISTS() macro to check the existence of a certain database. |
 |
Introduction to Metadata Functions
A metadata is a piece of information held by an object. Microsoft SQL Server (or rather Transact-SQL) provides many functions that use the concept of metadata to provide information about many objects of a database or of a database server.
Getting the Identifier of an Object
A database contains many types of objects. When you create one of them, the database engine creates an internal identifier for it (and stores that name somewhere). This allows the database engine to identify all objects stored in all databases. To let you know the identifier of an object, Transact-SQL provides a function named OBJECT_ID. Its syntax is:
OBJECT_ID('[ database_name . [ schema_name ] . | schema_name . ]
object_name' [ ,'object_type' ]);
This function takes two arguments that are passed as strings. The first argument is required and the second is optional. The first argument, object_name, is the name of an object such as a table. Here is an example:
SELECT COLUMNPROPERTY(OBJECT_ID(N'Employees'),
N'LastName',
N'precision')AS [Maximum Number of Characters];
GO
If the object belongs to a schema (other than dbo), precede its name with it. You can also completely qualify the name of the object by preceding the schema with the name of its database.
The second argument of the OBJECT_ID() function allows you to specify the type of object of the object_name argument. It is passed as one or two letters depending on the object.
Every time you create a new database, it gets added to the master system database and your new database can be located using sys.databases. Also, when the new database is added to the master database, it automatically receives an integer that uniquely identifies it.
To know the identifier of a database, you an call the DB_ID() function. Its syntax is:
DB_ID ( [ 'database_name' ] ) RETURNS int;
This function takes an argument that is optional. If you call the function without an argument, it returns the name of the current database (the database that is currently selected). Here is an example:
SELECT DB_ID() AS [Selected Database]; GO
To find out the identifier of an existing database, pass the name of that database as argument. Here is an example:
SELECT DB_ID(N'KoloBank') AS [ID For Kolo Bank]; GO
In reality, when you pass an argument, the database engine would look for a database with that name. If the database is found, the function returns its number. If there is no database with that name, the function returns NULL (it doesn't produce an error).
At any time, to find out the name of the database that is currently used, call the DB_NAME() function. Its syntax is:
DB_NAME ( [ database_id ] ) RETURNS nvarchar(128);
This function takes an optional argument. If you call this function without an argument, it finds the name of the database that is currently selected and produces it. Here is an example:
SELECT DB_NAME(); GO
If you want, pass an integer that represents the index (in the normal English sense, not in database sense) of the database. Normally, here are the databases produced by some of the arguments:
| If you pass the argument as | The function would produce |
| 1 | master |
| 2 | tempdb |
| 3 | model |
| 4 | msdb |
| 5 | ReportServer |
| 6 | ReportServerTempDB |
The other numbers would ask the database engine to get the database that corresponds to the number of the argument. In fact, you can use a call to DB_ID and pass it as argument. Here is an example:
SELECT DB_NAME(DB_ID(N'KoloBank')) AS [Kolo Bank]; GO
The Identifier of the Current User
Transact-SQL provides various functions to let you know the name of the current user, that is, the user name of the person who is currently logged on. To let you get the identifier of the user, Transact-SQL provides two functions and you can use either of them. The functions are SUSER_ID and SUSER_SID. Their syntaxes are:
SUSER_ID ( [ 'login' ] ) RETURNS int; SUSER_SID ( [ 'login' ] ) RETURNS varbinary(85);
These functions produce a number that identies the user but that value may not be particularly significant to you. Here is an example of calling the SUSER_ID() function:
The Identifier of the Current User
To let you get the actual username of the user, Transact-SQL provides a function named SUSER_NAME. An alternate function is named SUSER_SNAME. Their syntaxes are:
SUSER_NAME ( [ server_user_id ] ) RETURNS nvarchar(128); SUSER_SNAME ( [ server_user_sid ] ) RETURNS nvarchar(128)
You can use any of these functions to get the username.
To let you get the name of the computer that is currently being used, Transact-SQL provides a function named HOST_NAME. Its syntax is:
HOST_NAME() RETURNS nvarchar(128);
Here is an example:
Fundamentals of String-Based Functions
Introduction
To assist you with managing strings or performing operations on them, Transact-SQL provides various functions. The functions are divided in categories that include character-based, conversions, addition, sub-strings, etc.
The length of a string is the number of characters or symbols it contains. 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 nvarchar(120)
SET @FIFA = N'Fédération Internationale de Football Association'
SELECT @FIFA AS FIFA
SELECT LEN(@FIFA) AS [Number of Characters]
This would produce:
Concatenating Two Strings
Concatenating two strings consists of adding one string to the other. You can do this using the addition operator '+'. Here are examples:
DECLARE @FirstName nvarchar(24),
@LastName nvarchar(24),
@FullName nvarchar(50),
@CompleteName nvarchar(50);
SET @FirstName = N'Jerome ';
SET @LastName = N'Ansfield';
SET @FullName = @FirstName + @LastName;
SET @CompleteName = N'Anne ' + N'Sanders';
SELECT N'Jeffrey ' + N'Erschbamer';
SELECT @FullName [Full Name];
SELECT @CompleteName [Complete Name];
GO
This would produce:
In the same way, you can concatenate various strings by using the addition operator between them. Here are examples:
DECLARE @FirstName nvarchar(24), @LastName nvarchar(24), @FullName nvarchar(50), @CompleteName nvarchar(50); SET @FirstName = N'Jerome'; SET @LastName = N'Ansfield'; SET @FullName = @LastName + N', ' + @FirstName; SET @CompleteName = N'Anne' + N' ' + N'Juliette' + N' ' + N'Sanders'; SELECT @FullName [Full Name]; SELECT @CompleteName [Complete Name]; SELECT N'Jeffrey' + N' ' + N'Anselme' + N' ' + N'Erschbamer'; GO
This would produce:
In previous lessons, we saw a function named CONCAT. Transact-SQL provides that function as a good tool to add values of any types to produce a string. The syntax of that function is:
CONCAT(value_1, value_2, . . ., value_n]) RETURNS NVARCHAR();
This function takes an unlimited number of values as arguments. Those arguments are separated by comas. The function returns a string. Here are examples:
DECLARE @FirstName nvarchar(24),
@LastName nvarchar(24),
@FullName nvarchar(50),
@CompleteName nvarchar(50);
SET @FirstName = N'Jerome ';
SET @LastName = N'Ansfield';
SET @FullName = CONCAT(@FirstName, @LastName);
SET @CompleteName = CONCAT(N'Anne ', N'Sanders');
SELECT CONCAT(N'Jeffrey ', N'Erschbamer');
SELECT @FullName [Full Name];
SELECT @CompleteName [Complete Name];
GO
Here is another example where the function takes more than two arguments:
DECLARE @FirstName nvarchar(24), @LastName nvarchar(24), @FullName nvarchar(50), @CompleteName nvarchar(50); SET @FirstName = N'Jerome'; SET @LastName = N'Ansfield'; SET @FullName = CONCAT(@LastName, N', ', @FirstName); SET @CompleteName = CONCAT(N'Anne', N' ', N'Juliette', N' ', N'Sanders'); SELECT @FullName [Full Name]; SELECT @CompleteName [Complete Name]; SELECT CONCAT(N'Jeffrey', N' ', N'Anselme', N' ', N'Erschbamer'); GO
Strings and Type Conversions
In Transact-SQL and Microsoft Windows, there is a default way that each type of value should display. Sometimes you want the value to display some other way. To assist you with this, Transact-SQL provides a function named FORMAT. Its syntax is:
FORMAT(Type value, nvarchar format [, nvarchar culture ] ) RETURNS nvarchar
The FORMAT() function allows you to specify how you want a value to be presented to a user. This function can be applied to various types of values such as integers, floating-point numbers, etc. Therefore, the first argument of this function depends on the type of value you are using.
The second argument specifies the format you want to apply to the first argument. The third argument is optional. It is important to keep two issues in mind:
Probably the primary types of values you would use are natural numbers. You can display such numbers with their digits only. Here is an example:
DECLARE @YearlySalary int,
@FiscalBudget bigint;
SET @YearlySalary = 48626;
SET @FiscalBudget = 12640685;
SELECT @YearlySalary AS "Yearly Salary",
@FiscalBudget AS "Company Budget";
GO
Pass the value as the second argument and pass the second argument as:
DECLARE @HourlySalary int,
@Distance real;
SET @HourlySalary = 28.65;
SET @Distance = 146.704;
SELECT FORMAT(@HourlySalary, N'D') AS "Hourly Salary",
FORMAT(@Distance, N'D') AS Distance;
GO
DECLARE @DiscountRate int,
@RateOfDiscount int,
@DiscountPart real,
@InterestRate decimal(8, 4);
SET @DiscountRate = 25.50;
SET @RateOfDiscount = 0.2550;
SET @DiscountPart = 0.2550;
SET @InterestRate = 0.1295181;
SELECT FORMAT(@DiscountRate, N'P') "Discount Rate",
FORMAT(@RateOfDiscount, N'P') "Discount Rate",
FORMAT(@DiscountPart, N'P') "Discount Rate",
FORMAT(@InterestRate, N'p') N'Interest Rate';
GO
DECLARE @HourlySalary int,
@UnitPrice decimal(8, 4),
@FiscalBudget bigint;
SET @HourlySalary = 28.65;
SET @UnitPrice = 349.95;
SET @FiscalBudget = 12640685;
SELECT FORMAT(@HourlySalary, N'E') AS "Hourly Salary",
FORMAT(@UnitPrice, N'e') [Unit Price],
FORMAT(@FiscalBudget, N'E') AS N'Company Budget';
GO
DECLARE @HourlySalary int,
@UnitPrice decimal(8, 4),
@FiscalBudget bigint;
SET @HourlySalary = 28.65;
SET @UnitPrice = 349.95;
SET @FiscalBudget = 12640685;
SELECT FORMAT(@HourlySalary, N'C') AS "Hourly Salary",
FORMAT(@UnitPrice, N'C') [Unit Price],
FORMAT(@FiscalBudget, N'c') AS N'Company Budget';
GO
By default, when you call the FORMAT() function, the database engine refers to the language used on the computer and the settings of the Control Panel. If you want to use the formats of another language, pass the third argument, culture. This argument, passed as a string, specifies the language whose rules the function should follow. Here are examples:
DECLARE @HourlySalary money,
@Wage money,
@UnitPrice decimal(8, 4),
@FiscalBudget bigint;
SET @HourlySalary = 28.65;
SET @Wage = 28.65;
SET @UnitPrice = 349.95;
SET @FiscalBudget = 12640685;
SELECT FORMAT(@HourlySalary, N'C', N'en-CA') AS "Hourly Salary",
FORMAT(@Wage, N'C', N'fr-ca') AS "Hourly Salary",
FORMAT(@UnitPrice, N'c', N'en-gb') [Unit Price],
FORMAT(@FiscalBudget, N'C', N'es-ES') AS N'Company Budget';
GO
As you may know already, a string can be made of uppercase letters, lowercasel letters, and symbols that don't have a particular case. To let you convert a character or a string to lowercase, Transact-SQL provides a function named LOWER. Its syntax is:
LOWER(String) RETURNS varchar;
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 the conversion, the LOWER() function returns a new string. Here is an example:
DECLARE @FIFA varchar(120)
SET @FIFA = N'Fédération Internationale de Football Association'
SELECT @FIFA AS FIFA
SELECT LOWER(@FIFA) AS Converted
This would produce:
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 from the left side of a known string. To let you get the left sub-string of a string, Transact-SQL provides a function named LEFT. Its syntax is:
LEFT(string, number-of-characters) RETURNS varchar
This function takes two arguments. The first argument specifies the original string. The second argument specifies the number of characters from the most-left side that will constitute the sub-string. After the operation, the LEFT() function returns a new string made of the left character + the number-of-characters on its right from the string.
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 a function named RIGHT. Its syntax is:
RIGHT(string, NumberOfCharacters) RETURNS varchar;
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.
Replacing Occurrences in a String
To let you replace one character or a sub-string from a string, Transact-SQL provides a function named REPLACE. Its syntax is: REPLACE(string, find-string, replace-with) RETURNS varchar; or REPLACE(string, find-string, replace-with) RETURNS binary; This function takes three arguments. The first is the string that will be used as reference. The second argument, find-string, is a character or a sub-string to look for in the string argument. If the find-string character or sub-string is found in the string, then it is replaced with the value of the last argument, replace-with. |
 |
Arithmetic Functions
To let you find out if a value is positive or negative, Transact-SQL provides a function named SIGN. 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:
DECLARE @Number As int;
SET @Number = 24.75;
SELECT SIGN(@Number) AS [Sign of 1058];
GO |
 |
DECLARE @Number As int;
SET @Number = 0;
SELECT SIGN(@Number) AS [Sign of Number];
GO
|
 |
DECLARE @Number As int;
SET @Number = -57.05;
SELECT SIGN(@Number) AS [Sign of -57.05];
GO |
 |
The Absolute Value of a Number
To let you get the absolute value of a number, Transact-SQL provides a function named ABS. Its syntax is:
ABS(expression) RETURNS Data Type of Argument
This function takes an expression or a number as argument. The function returns the absolute value of the argument. Here is an example:
DECLARE @NumberOfStudents INTEGER;
SET @NumberOfStudents = -32;
SELECT ABS(@NumberOfStudents) AS [Number of Students];
GO
This would produce:
To let you get the ceiling of a number, Transact-SQL provides a function named CEILING. Its syntax is:
CEILING(expression)
This function takes as argument a number or an expression that can evaluate to a number. After the conversion, if the function succeeds, it returns a double-precision number that is greater than or equal to expression. Here is an example:
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
This would produce:
Here is another way of displaying the above results:
DECLARE @Number1 As Numeric(6, 2),
@Number2 As Numeric(6, 2)
SET @Number1 = 12.155;
SET @Number2 = -24.06;
PRINT N'The ceiling of 12.155 is ' +
TRY_CONVERT(varchar(10), CEILING(@Number1));
PRINT N'The ceiling of -24.06 is ' +
TRY_CONVERT(varchar(10), CEILING(@Number2));
GO
This would produce:
To let you finding the floor of a number, Transact-SQL a function named FLOOR. Its syntax is:
FLOOR(expression)
The FLOOR() function takes as argument a numeric value or an expression that can be evaluated to a number. If the function succeeds during its conversion, it produces the integer that is the floor of the argument. Here is an example:
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
This would produce:
To calculate the exponential value of a number, Transact-SQL provides a function named EXP. Its syntax is:
EXP(expression)
This function takes one argument as a number or an expression that can be evaluated to a number. Here is an example:
DECLARE @Number As Numeric(6, 2);
SET @Number = 6.48;
SELECT EXP(@Number) AS [Exponent of 6.48];
GO
This would produce:
To let you find the power of a number, Transact-SQL provides a function named POWER. Its syntax is:
POWER(x, y)
This function takes two required arguments. The first argument, x, is used as the base number to be evaluated. The second argument, y, also called the exponent, will raise x to this value. Here is an example:
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
This would produce:
Geometric Values
Introduction
Transact-SQL support geometric coordinates through a data type named geometry. You can use it to declare a variable. Here is an example:
DECLARE @Location geometry;
The geometry type is a class with properties and methods. After declaring a geometry variable, you must initialize it. The most fundamental action is to initialize the variable. This is done through the STGeomFromText method whose syntax is:
static geometry STGeomFromText('geography_tagged_text', SRID)
The method is static. This means that, to access it, you use geometry::STGeomFromText. Here is an example:
DECLARE @Location geometry; SET @Location = geometry::STGeomFromText(. . .)
This method takes two arguments. The first argument holds a value identified as a Well-Known Text (WKT) value. The value follows a format defined by OGC. There are various you can specify this value. As you may know already, a geometric point is an object that has two values: the horizontal coordinate x and the vertical coordinate y. The value can be integers or flowing-point numbers.
If you know the coordinates of a point and you want to use it as the value of the geometry object, type point() (or POINT(), this is not case-sensitive) and, in the parentheses, type both values separated by a space. Here is an example:
DECLARE @Location geometry;
SET @Location = geometry::STGeomFromText('point(6 4)', . . .
Instead of just one point, you may want to use a geometric value that is a line. In this case, specify the shape as linestring(, ). In the parentheses and on both sides of the comma, type each point as x and y. Here is an example:
DECLARE @Location geometry;
SET @Location = geometry::STGeomFromText('linestring(1 4, 5 2)', . . .);
You can also use a complex geometric, in which case you can pass the argument as a polygon. Use polygon(()) (or POLYGON(())) and pass the vertices in the parentheses. Each vertext should specify its x and y coordinates. The vertices are separated by commas. A last vertex should be used to close the polygon, in which case the first and the last vertices should be the same. Here is an example:
DECLARE @Location geometry;
SET @Location = geometry::STGeomFromText('polygon((1 2, 2 5, 5 5, 4 2, 1 2))', . . );
The second argument of the geometry::STGeomFromText method is a contant integer known as the spatial reference ID (SRID).
After declaring and initializing the value, you can use a SELECT statement to display its value. Here is an example:
DECLARE @Location geometry;
SET @Location = geometry::STGeomFromText('point(6 4)', 0);
SELECT @Location;
Representing a Geographical Location
Transact-SQL supports geographical locations.
Other Types of Values
A Boolean value is one that indicates True or False. To support a value, Transact-SQL provides a data type named BIT or bit. You can use that type to declare a variable. Here is an example:
DECLARE @IsOrganDonor bit;
After declaring a Boolean variable, you can initialize it with 0 or another value. If the variable is initialized with 0, it receives the Boolean value of False. If it is initialized with any other number, it receives a True value. Here is an example of using a Boolean variable:
|
||||
USE master; GO DROP DATABASE University2; GO DROP DATABASE University3; GO CREATE DATABASE University4; GO USE University4; GO CREATE SCHEMA Academics; GO
Practical Learning: Casting a Value
DECLARE @StrSalary nvarchar(10), @StrHours nvarchar(6), @WeeklySalary decimal(6,2) SET @StrSalary = N'2W2.18'; SET @StrHours = N'38.50'; SET @WeeklySalary = CAST(@StrSalary As Decimal(6,2)) * CAST(@StrHours As Decimal(6,2)); SELECT @WeeklySalary; GO
DECLARE @StrSalary nvarchar(10),
@StrHours nvarchar(6),
@WeeklySalary decimal(6,2)
SET @StrSalary = N'2W2.18';
SET @StrHours = N'38.50';
SET @WeeklySalary = TRY_CAST(@StrSalary As Decimal(6,2)) *
CAST(@StrHours As Decimal(6,2));
SELECT @WeeklySalary;
GO
DECLARE @StrSalary nvarchar(10), @StrHours nvarchar(6), @WeeklySalary decimal(6,2) SET @StrSalary = N'242.18'; SET @StrHours = N'38.50'; SET @WeeklySalary = TRY_CAST(@StrSalary As Decimal(6,2)) * TRY_CAST(@StrHours As Decimal(6,2)); SELECT @WeeklySalary; GO
Practical Learning: Converting a Value
-- 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 N'Square Characteristics';
PRINT N'-----------------------';
PRINT N'Side = ' + TRY_CONVERT(varchar(10), @Side, 10);
PRINT N'Perimeter = ' + TRY_CONVERT(varchar(10), @Perimeter, 10);
PRINT N'Area = ' + TRY_CONVERT(varchar(10), @Area, 10);
GOPractical Learning: Concatenating Two Columns
USE University4;
GO
CREATE TABLE Academics.UndergraduateStudents
(
StudentNumber nvarchar(10) not null,
FirstName nvarchar(20),
MiddleName nvarchar(20),
LastName nvarchar(20),
EmployeeName AS CONCAT(LastName, N', ', FirstName),
Username nvarchar(12),
[Password] nvarchar(20) DEFAULT N'Password1',
Gender nvarchar(20)
);
GOPractical Learning: Converting a String to Lowercase
USE University4; GO -- =================================================================== -- Function: CreateUsername -- This function creates a user name based on the first and last names -- =================================================================== CREATE FUNCTION Academics.CreateUsername (@FirstName nvarchar(25), @LastName nvarchar(25)) RETURNS varchar(50) AS BEGIN DECLARE @Username AS nvarchar(50); SELECT @Username = LOWER(@FirstName) + LOWER(@LastName); RETURN @Username; END GO
SELECT University4.Academics.CreateUsername(N'Francine', N'Mukoko'); GO
Practical Learning: Creating a Sub-String With Left Characters
USE University4; GO DROP FUNCTION Academics.CreateUsername; GO CREATE FUNCTION Academics.CreateUsername (@FirstName varchar(25), @LastName varchar(25)) RETURNS varchar(26) AS BEGIN DECLARE @Username AS varchar(26); SELECT @Username = LOWER(LEFT(@FirstName, 1) + LEFT(@LastName, 4)); RETURN @Username; END GO
SELECT University4.Academics.CreateUsername(N'Francine', N'Mukoko'); GO
USE University4;
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, Username, Gender)
VALUES(N'25506037', N'Matthew', N'Andy', N'Agurs', Academics.CreateUsername(N'Matthew', N'Agurs'), N'Male'),
(N'88167413', N'Thomas', N'Harold', N'Aldredge', Academics.CreateUsername(N'Thomas', N'Aldredge'), N'Male');
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, Username, Gender)
VALUES(N'63358761', N'Martin', N'Aldredge', Academics.CreateUsername(N'Martin', N'Aldredge'), N'Male'),
(N'92739470', N'Maggie', N'Altobello', Academics.CreateUsername(N'Maggie', N'Altobello'), N'Female');
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, Username, Gender)
VALUES(N'64702716', N'Frank', N'Alton', Academics.CreateUsername(N'Frank', N'Alton'), N'Male'),
(N'25117160', N'Freida', N'Alvarez', Academics.CreateUsername(N'Freida', N'Alvarez'), N'Female');
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, Username)
VALUES(N'30493386', N'Hahia', N'Kwok', Academics.CreateUsername(N'Hahia', N'Kwok'));
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, Username, Gender)
VALUES(N'69631417', N'Simonne', N'Alvaro', Academics.CreateUsername(N'Simonne', N'Alvaro'), N'Female');
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, Username, Gender)
VALUES(N'76088478', N'Tricia', N'Catherine', N'Anderson', Academics.CreateUsername(N'Tricia', N'Anderson'), N'Female');
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, Username, Gender)
VALUES(N'77630549', N'Patrick', N'Amaleck', Academics.CreateUsername(N'Patrick', N'Amaleck'), N'Male'),
(N'29502309', N'Maria', N'Anderson', Academics.CreateUsername(N'Maria', N'Anderson'), N'Female');
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, Username, Gender)
VALUES(N'68903849', N'Katherine', N'Jennie', N'Sandford', Academics.CreateUsername(N'Katherine', N'Sandford'), N'Female');
GOPractical Learning: Getting the Right Characters
-- ================================================== -- Function: Last4DigitsOfSSN -- Description: This function finds the last 4 digits -- of a US Social Security Number -- ================================================== CREATE SCHEMA Administration; GO CREATE FUNCTION Administration.Last4DigitsOfSSN(@SSN nvarchar(12)) RETURNS nchar(4) AS BEGIN RETURN RIGHT(@SSN, 4); END GO
SELECT University4.Administration.Last4DigitsOfSSN(N'836483846'); GO
Practical Learning: Replacing Characters or Sub-Strings
DROP FUNCTION Administration.Last4DigitsOfSSN; GO CREATE FUNCTION Administration.GetLast4DigitsOfSSN(@SSN varchar(12)) RETURNS char(4) AS BEGIN DECLARE @StringWithoutSymbol As varchar(12); -- First remove empty spaces SET @StringWithoutSymbol = REPLACE(@SSN, N' ', N''); -- Now remove the dashes "-" if they exist SET @StringWithoutSymbol = REPLACE(@StringWithoutSymbol, N'-', N''); RETURN RIGHT(@StringWithoutSymbol, 4); END GO
SELECT University4.Administration.GetLast4DigitsOfSSN(N'244-04-8502'); GO
Practical Learning: Ending the Lesson
|
|
|||
| Previous | Copyright © 2008-2025, FunctionX | Last Update: Monday 07 April 2025, 18:17 | Next |
|
|
|||