SQL Programming
  • Introduction to SQL
  • Data Definition Language
    • DATA TYPES
    • CREATE DATABASE
    • DROP DATABASE
    • CREATE TABLE Statement
    • CONSTRAINTS
      • SQL PRIMARY KEY Constraint
      • SQL NOT NULL Constraint
      • SQL UNIQUE Constraint
      • SQL FOREIGN KEY Constraint
      • SQL DEFAULT Constraint
      • SQL CHECK Constraint
    • DROP & TRUNCATE TABLE Statement
    • ALTER TABLE Statement
    • AUTO INCREMENT Field
    • SQL SELECT INTO Statement
    • CREATE INDEX
    • DDL Practice Problems
  • Data Manipulation Language
    • INSERT INTO Statement
    • INSERT INTO SELECT Statement
    • DELETE Statement
    • UPDATE Statement
    • DML Practice Problems
  • SQL NULL Values
  • SQL Comments
  • Data Query Language
    • SQL SELECT DISTINCT Statement
    • SQL WHERE Clause
      • SQL And, Or, Not (Logical Operators)
      • SQL Wildcards
      • SQL LIKE Operator
      • SQL IN Operator
      • SQL BETWEEN Operator
      • SQL WHERE Exercises
    • SQL TOP CLAUSE
    • SQL Order By
    • SQL Server Scalar Functions (Non-aggregate)
      • SQL MIN() and MAX()
      • SQL COUNT(), AVG() and SUM() Functions
      • Functions Problems
    • SQL ISNULL Functions
    • SQL Aliases
    • SQL CASE Statement
    • SQL SET Operator
    • SQL GROUP BY Statement
    • SQL HAVING Clause
    • Analytical Function
    • SQL Joins
      • SQL INNER JOIN Keyword
      • SQL LEFT JOIN Keyword
      • SQL RIGHT JOIN Keyword
      • SQL FULL OUTER JOIN Keyword
      • SQL Self JOIN
      • SQL CROSS JOIN
    • SQL Subqueries
    • SQL EXISTS Operator
    • SQL ANY and ALL Operators
  • CREATE VIEWS
  • Common Table Expressions (CTE)
  • Data Control Language
  • SQL Stored Procedures for SQL Server
  • Practice Papers
  • SQL PROJECT
Powered by GitBook
On this page
  • SQL Server String Functions
  • SQL Server Math/Numeric Functions
  • SQL Server Date Functions
  • SQL Server Advanced Functions

Was this helpful?

  1. Data Query Language

SQL Server Scalar Functions (Non-aggregate)

SQL Server has many built-in functions.

This reference contains string, numeric, date, conversion, and some advanced functions in SQL Server.

SQL Server String Functions

Function

Description

Syntax

CHAR

Returns the character based on the ASCII code

SELECT CHAR(65) AS CodeToCharacter;

CHARINDEX

Returns the position of a substring in a string

SELECT CHARINDEX('t', 'Customer') AS MatchPosition;

CONCAT

Adds two or more strings together

SELECT CONCAT('Gyansetu', '.com');

Concat with +

Adds two or more strings together

SELECT 'Gyansetu' + '.com';

CONCAT_WS

Adds two or more strings together with a separator

SELECT CONCAT_WS('.', 'www', 'Gyansetu', 'com');

LEFT

Extracts a few characters from a string (starting from left)

SELECT LEFT('SQL Tutorial', 3) AS ExtractString;

LEN

Returns the length of a string

SELECT LEN('Gyansetu.com');

LOWER

Converts a string to lower-case

SELECT LOWER('SQL Tutorial is FUN!');

LTRIM

Removes leading spaces from a string

SELECT LTRIM(' SQL Tutorial') AS LeftTrimmedString;

REPLACE

Replaces all occurrences of a substring within a string, with a new string

SELECT REPLACE('SQL Tutorial', 'T', 'M');

RIGHT

Extracts a few characters from a string (starting from right)

SELECT RIGHT('SQL Tutorial', 3) AS ExtractString;

RTRIM

Removes trailing spaces from a string

SELECT RTRIM('SQL Tutorial ') AS RightTrimmedString;

STR

Returns a number as string

SELECT STR(185);

SUBSTRING

Extracts some characters from a string

SELECT SUBSTRING('SQL Tutorial', 1, 3) AS ExtractString;

TRIM

Removes leading and trailing spaces (or other specified characters) from a string

SELECT TRIM(' SQL Tutorial! ') AS TrimmedString;

UPPER

Converts a string to upper-case

SELECT UPPER('SQL Tutorial is FUN!');

SQL Server Math/Numeric Functions

Function

Description

Syntax

ABS

Returns the absolute value of a number

SELECT Abs(-243.5) AS AbsNum;

AVG

Returns the average value of an expression

SELECT AVG(Price) AS AveragePrice FROM Products;

CEILING

Returns the smallest integer value that is >= a number

SELECT CEILING(25.75) AS CeilValue;

COUNT

Returns the number of records returned by a select query

SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;

FLOOR

Returns the largest integer value that is <= to a number

SELECT FLOOR(25.75) AS FloorValue;

MAX

Returns the maximum value in a set of values

SELECT MAX(Price) AS LargestPrice FROM Products;

MIN

Returns the minimum value in a set of values

SELECT MIN(Price) AS SmallestPrice FROM Products;

POWER

Returns the value of a number raised to the power of another number

SELECT POWER(4, 2);

ROUND

Rounds a number to a specified number of decimal places

SELECT ROUND(235.415, 2) AS RoundValue;

SQRT

Returns the square root of a number

SELECT SQRT(64);

SQUARE

Returns the square of a number

SELECT SQUARE(64);

SUM

Calculates the sum of a set of values

SELECT SUM(quantity) as TotalItemsOrdered FROM OrderDetails

SQL Server Date Functions

Function

Description

Syntax

CURRENT_TIMESTAMP

Returns the current date and time

SELECT CURRENT_TIMESTAMP;

DATEADD

Adds a time/date interval to a date and then returns the date

SELECT DATEADD(year, 1, '2017/08/25') AS DateAdd;

DATEDIFF

Returns the difference between two dates

SELECT DATEDIFF(year, '2017/08/25', '2011/08/25') AS DateDiff;

DATEPART

Returns a specified part of a date (as integer)

SELECT DATEPART(year, '2017/08/25') AS DatePartInt;

DAY

Returns the day of the month for a specified date

SELECT DAY('2017/08/25') AS DayOfMonth;

GETDATE

Returns the current database system date and time

SELECT GETDATE();

ISDATE

Checks an expression and returns 1 if it is a valid date, else 0

SELECT ISDATE('2017-08-25');

MONTH

Returns the month part for a specified date (1 - 12)

SELECT MONTH('2017/08/25') AS Month;

SYSDATETIME

Returns the date and time of the SQL Server

SELECT SYSDATETIME() AS SysDateTime;

YEAR

Returns the year part for a specified date

SELECT YEAR('2017/08/25') AS Year;

SQL Server Advanced Functions

Function

Description

Syntax

CAST

Converts a value (of any type) into a specified datatype

CAST(expression AS datatype(length)) SELECT CAST(25.65 AS varchar); SELECT CAST('2017-08-25' AS datetime);

CONVERT

Converts a value (of any type) into a specified datatype

CONVERT(data_type(length), expression, style) SELECT CONVERT(int, 25.65); SELECT CONVERT(varchar, '2017-08-25', 101);

ISNULL

Return a specified value if the expression is NULL, otherwise return the expression

SELECT ISNULL(NULL, 'Gyansetu.com');

PreviousSQL Order ByNextSQL MIN() and MAX()

Last updated 6 years ago

Was this helpful?

16KB
Useful SQL Server Functions.docx