SQL Server: Literals

387 views

This SQL Server tutorial explains how to use literals (strings, integers, decimals, and datetime values) in SQL Server with examples.

Description

In SQL Server, a literal is the same as a constant. We'll cover several types of literals - string, integer, decimal, and datetime literals.

String Literals

String literals are always surrounded by single quotes (').

For example:

'TechOnTheNet.com'
'ABC'
'123'

These string literal examples contain of strings enclosed in single quotes. But what happens if you want your string to also contain a single quote within the text?

For example:

'TechOnTheNet.com is great! It's my favorite site!'

In this example, the literal string contains a single quote in the word, It's. This additional single quote will cause the literal to terminate at the second quote, as follows (and possibly result in an error being raised):

'TechOnTheNet.com is great! It'

To workaround string literals that contain single quotes, you need to escape the single quote with an additionl single quote as follows:

'TechOnTheNet.com is great! It''s my favorite site!'

Integer Literals

Integer literals can be either positive numbers or negative numbers, but do not contain decimals. If you do not specify a sign, then a positive number is assumed. Here are some examples of valid integer literals:

2014
+2014
-2014

Decimal Literals

Decimal literals can be either positive numbers or negative numbers and contain decimals. If you do not specify a sign, then a positive number is assumed. Here are some examples of valid decimal literals:

2014.6
+2014.6
-2014.6

Datetime Literals

Datetime literals are character representations of datetime values that are enclosed in single quotes. Here are some examples of valid datetime literals:

'May 12, 2014'
'2012/05/12'
'2014/05/12 09:49:12'

posted Feb 23, 2017 by Shivaranjini

Promote This Article

Facebook Share Button

Twitter Share Button

LinkedIn Share Button

Related Articles

SQL Server: Difference between floor, ceiling and round

In SQL queries sometimes we need to round off some decimal or float values, at that time we always think that which option to be applied while we have three different kinds of system defined SQL rounding functions-Ceiling, Floor and Round.

CEILING

Get the value on the right side of the decimal and returns the smallest integer greater or equal to, the specified values.

FLOOR

Get the value on the right side of the decimal and returns the largest integer less or equal to the specified values (only number)

ROUND

Rounds a positive or negative value to a specific length.

Example of SQL rounding functions i.e. floor, ceiling and round

READ MORE

SQL Server: ROUND Function

This SQL Server tutorial explains how to use the ROUND function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the ROUND function returns a number rounded to a certain number of decimal places.

Syntax

The syntax for the ROUND function in SQL Server (Transact-SQL) is:

ROUND( number, decimal_places [, operation ] )

Parameters or Arguments

number

The number to round.

decimal_places

The number of decimal places rounded to. This value must be a positive or negative integer. If this parameter is omitted, the ROUND function will round the number to 0 decimal places.

operation

Optional. The operation can be either 0 or any other numeric value. When it is 0 (or this parameter is omitted), the ROUND function will round the result to the number of decimal_places. If operation is any value other than 0, the ROUND function will truncate the result to the number of decimal_places.

Note

If the operation parameter is 0 (or not provided), the ROUND function will round the result to the number of decimal_places.
If the operation parameter is non-zero, the ROUND function will truncate the result to the number of decimal_places.
See also the CEILING and FLOOR functions.

Applies To

The ROUND function can be used in the following versions of SQL Server (Transact-SQL):

SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example

Let's look at some SQL Server ROUND function examples and explore how to use the ROUND function in SQL Server (Transact-SQL).

For example:

SELECT ROUND(125.315, 2);
Result: 125.320    (result is rounded because 3rd parameter is omitted)

SELECT ROUND(125.315, 2, 0);
Result: 125.320    (result is rounded because 3rd parameter is 0)

SELECT ROUND(125.315, 2, 1);
Result: 125.310    (result is truncated because 3rd parameter is non-zero)

SELECT ROUND(125.315, 1);
Result: 125.300    (result is rounded because 3rd parameter is omitted)

SELECT ROUND(125.315, 0);
Result: 125.000    (result is rounded because 3rd parameter is omitted)

SELECT ROUND(125.315, -1);
Result: 130.000    (result is rounded because 3rd parameter is omitted)

SELECT ROUND(125.315, -2);
Result: 100.000    (result is rounded because 3rd parameter is omitted)

READ MORE

SQL Server: FLOOR Function

This SQL Server tutorial explains how to use the FLOOR function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the FLOOR function returns the largest integer value that is equal to or less than a number.

Syntax

The syntax for the FLOOR function in SQL Server (Transact-SQL) is:

FLOOR( number )

Parameters or Arguments

number

The value used to determine the largest integer value that is equal to or less than a number.

Note

See also the CEILING and ROUND functions.

Applies To

The FLOOR function can be used in the following versions of SQL Server (Transact-SQL):

SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example

Let's look at some SQL Server FLOOR function examples and explore how to use the FLOOR function in SQL Server (Transact-SQL).

For example:

SELECT FLOOR(5.9);
Result: 5

SELECT FLOOR(34.29);
Result: 34

SELECT FLOOR(-5.9);
Result: -6

READ MORE

SQL Server: CEILING Function

This SQL Server tutorial explains how to use the CEILING function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the CEILING function returns the smallest integer value that is greater than or equal to a number.

Syntax

The syntax for the CEILING function in SQL Server (Transact-SQL) is:

CEILING( number )

Parameters or Arguments

number

The number used to find the smallest integer value.

Note

See also the FLOOR and ROUND functions.

Applies To

The CEILING function can be used in the following versions of SQL Server (Transact-SQL):

SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example

Let's look at some SQL Server CEILING function examples and explore how to use the CEILING function in SQL Server (Transact-SQL).

For example:

SELECT CEILING(32.65);
Result: 33

SELECT CEILING(32.1);
Result: 33

SELECT CEILING(32);
Result: 32

SELECT CEILING(-32.65);
Result: -32

SELECT CEILING(-32);
Result: -32

READ MORE

Nested WITH clauses for Common Table Expressions in SQL Server

WITH x AS 
(
    SELECT * FROM MyTable
), 
y AS 
(
    SELECT * FROM x
)
SELECT * FROM y

READ MORE

...