SQL Server Functions Reference for Data Manipulation
Explore the comprehensive guide on SQL Server functions covering string, numeric, date, conversion, and advanced functions. Learn about the differences between stored procedures and user-defined functions for data manipulation in SQL Server.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Lesson 42 SQL FUNCTIONS Trainer: Bach Ngoc Toan TEDU Website: http://tedu.com.vn
SQL SERVER FUNCTIONS SQL Server has many built-in functions. This reference contains the string, numeric, date, conversion, and advanced functions in SQL Server.
COMPARING WITH STORE PROCEDURE Store procedure (SP) Function (UDF User defined function) Function must return a single value (which may be a scalar or a table) We can't use transaction in UDF. Only input parameter. SP can return zero , single or multiple values. We can use transaction in SP. SP can have input/output parameter We can call function from SP. We can't use SP in SELECT/ We can't use SP in SELECT/ We can use exception handling using Try-Catch block in SP. We can't call SP from function. We can use UDF in SELECT/ WHERE/ HAVING statement We can't use Try-Catch block in UDF
TYPE OF FUNCTIONS SQL Server String Functions SQL Server Numeric Functions SQL Server Date Functions SQL Server Conversion Functions SQL Server Advanced Functions Custom functions
SQL SERVER STRING FUNCTIONS Functions CONCAT Description Concatenates two or more strings together Returns the length of the specified string Removes leading spaces from a string Removes trailing spaces from a string Extracts a substring from a string LEN LTRIM RTRIM SUBSTRING
SQL SERVER NUMERIC FUNCTIONS Functions MAX MIN SUM CEILING Description Returns the maximum value of an expression Returns the minimum value of an expression Returns the summed value of an expression Returns the smallest integer value that is greater than or equal to a number Returns the average value of an expression Returns the absolute value of a number AVG ABS
SQL SERVER DATE FUNCTIONS Review lesson: Working with date
SQL SERVER CONVERSION FUNCTIONS Function CAST Description Converts an expression from one data type to another Converts an expression from one data type to another CONVERT
SQL SERVER ADVANCED FUNCTIONS Function ISDATE Description Returns 1 if the expression is a valid date, otherwise 0 Lets you return an alternative value when an expression is NULL Returns 1 if the expression is a valid number, otherwise 0 Compares two expressions Returns the first non-null expression in a list ISNULL ISNUMERIC NULLIF COALESCE
USER-DEFINED FUNCTIONS Create function Execute function
