SQL Server Documentation

String Functions

These functions manipulate string data.

LEN(string_expression)

Returns the number of characters in a string, excluding trailing spaces.

SELECT LEN('Hello World') AS LengthOfString;
-- Result: 11

SUBSTRING(string_expression, start, length)

Extracts a part of a string.

SELECT SUBSTRING('SQL Server', 1, 3) AS SubString;
-- Result: SQL

UPPER(string_expression)

Converts a string to uppercase.

SELECT UPPER('microsoft') AS UpperCaseString;
-- Result: MICROSOFT

LOWER(string_expression)

Converts a string to lowercase.

SELECT LOWER('MICROSOFT') AS LowerCaseString;
-- Result: microsoft

Numeric Functions

These functions perform mathematical operations on numeric data.

ABS(numeric_expression)

Returns the absolute value of a number.

SELECT ABS(-123.45) AS AbsoluteValue;
-- Result: 123.45

ROUND(numeric_expression, length [, function])

Rounds a numeric value.

SELECT ROUND(123.456, 2) AS RoundedValue;
-- Result: 123.46

SQRT(numeric_expression)

Returns the square root of a positive number.

SELECT SQRT(25.0) AS SquareRoot;
-- Result: 5.0

Date and Time Functions

These functions work with date and time values.

GETDATE()

Returns the current database system date and time.

SELECT GETDATE() AS CurrentDateTime;
-- Result: 2023-10-27 10:30:00.123

DATEPART(datepart, date)

Returns an integer representing the specified part of a date.

SELECT DATEPART(year, '2023-10-27') AS YearPart;
-- Result: 2023

DATEDIFF(datepart, startdate, enddate)

Returns the difference between two dates in the specified datepart.

SELECT DATEDIFF(day, '2023-10-20', '2023-10-27') AS DaysDifference;
-- Result: 7

System Functions

These functions provide information about the SQL Server environment.

DB_NAME()

Returns the name of the current database.

SELECT DB_NAME() AS CurrentDatabaseName;
-- Result: AdventureWorks2019

USER_NAME()

Returns the user name for the specified user ID.

SELECT USER_NAME(1) AS UserNameForID1;
-- Result: dbo

Conversion Functions

These functions convert expressions from one data type to another.

CAST(expression AS data_type)

Converts an expression to a specified data type.

SELECT CAST(123 AS VARCHAR(10)) AS StringValue;
-- Result: '123'

CONVERT(data_type, expression [, style])

Similar to CAST, but offers more control over date formats with the optional style parameter.

SELECT CONVERT(VARCHAR, GETDATE(), 103) AS FormattedDate;
-- Result: '27/10/2023'