• Article

Expressions (Database Engine)

An expression is a combination of identifiers, values, and operators that SQL Server can evaluate to obtain a result. The data can be used in several different places when you are accessing or changing data. Expressions can be used, for example, as part of the data to retrieve in a query, or as a search condition when looking for data that meets a set of criteria.

An expression can be any of the following:

  • Constant

  • Function

  • Column name

  • Variable

  • Subquery

  • CASE, NULLIF, or COALESCE

An expression can also be built from combinations of these entities joined by operators.

In the following SELECT statement, for each row of the result set, SQL Server can resolve LastName to a single value. Therefore, it is an expression.

USE AdventureWorks;
GO
SELECT LastName 
FROM AdventureWorks.Person.Contact;

An expression can also be a calculation such as (price * 1.5) or (price + sales_tax).

In an expression, enclose character and datetime values in single quotation marks. In the following SELECT statement, the character literal B% that is used as the pattern for the LIKE clause must be in single quotation marks:

USE AdventureWorks;
GO
SELECT LastName, FirstName 
FROM AdventureWorks.Person.Contact 
WHERE LastName LIKE 'Bai%';
GO

In the following SELECT statement, the date value is enclosed in quotation marks.

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.HireDate 
FROM Person.Contact c JOIN HumanResources.Employee e 
ON c.ContactID = e.EmployeeID
WHERE e.HireDate = 'July 1, 2003';
GO

In the following example, more than one expression is used in the query. For example, Name, SUBSTRING, ProductNumber, ListPrice, and 1.5 are all expressions.

USE AdventureWorks;
GO
SELECT Name, 
   SUBSTRING('This is a long string', 1, 5) AS SampleText, 
   ProductNumber, 
   ListPrice * 1.5 AS NewPrice
FROM Production.Product;