SQL Data Types
Data types and ranges for Microsoft Access, MySQL and SQL
Server.
Microsoft Access Data Types
Data type
|
Description
|
Storage
|
Text
|
Use for text or combinations of text and numbers. 255 characters
maximum
|
|
Memo
|
Memo is used for larger amounts of text. Stores up to 65,536
characters. Note: You cannot sort a memo field. However,
they are searchable
|
|
Byte
|
Allows whole numbers from 0 to 255
|
1 byte
|
Integer
|
Allows whole numbers between -32,768 and 32,767
|
2 bytes
|
Long
|
Allows whole numbers between -2,147,483,648 and 2,147,483,647
|
4 bytes
|
Single
|
Single precision floating-point. Will handle most decimals
|
4 bytes
|
Double
|
Double precision floating-point. Will handle most decimals
|
8 bytes
|
Currency
|
Use for currency. Holds up to 15 digits of whole dollars, plus 4
decimal places. Tip: You can choose which country's currency
to use
|
8 bytes
|
AutoNumber
|
AutoNumber fields automatically give each record its own number,
usually starting at 1
|
4 bytes
|
Date/Time
|
Use for dates and times
|
8 bytes
|
Yes/No
|
A logical field can be displayed as Yes/No, True/False, or
On/Off. In code, use the constants True and False (equivalent to -1 and 0).Note: Null
values are not allowed in Yes/No fields
|
1 bit
|
Ole Object
|
Can store pictures, audio, video, or other BLOBs (Binary Large
OBjects)
|
up to 1GB
|
Hyperlink
|
Contain links to other files, including web pages
|
|
Lookup Wizard
|
Let you type a list of options, which can then be chosen from a
drop-down list
|
4 bytes
|
MySQL Data Types
In MySQL there are three main types : text, number, and Date/Time
types.
Text types:
Data type
|
Description
|
CHAR(size)
|
Holds a fixed length string (can contain letters, numbers, and
special characters). The fixed size is specified in parenthesis. Can store up
to 255 characters
|
VARCHAR(size)
|
Holds a variable length string (can contain letters, numbers,
and special characters). The maximum size is specified in parenthesis. Can
store up to 255 characters. Note: If you put a greater value
than 255 it will be converted to a TEXT type
|
TINYTEXT
|
Holds a string with a maximum length of 255 characters
|
TEXT
|
Holds a string with a maximum length of 65,535 characters
|
BLOB
|
For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of
data
|
MEDIUMTEXT
|
Holds a string with a maximum length of 16,777,215 characters
|
MEDIUMBLOB
|
For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes
of data
|
LONGTEXT
|
Holds a string with a maximum length of 4,294,967,295 characters
|
LONGBLOB
|
For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295
bytes of data
|
ENUM(x,y,z,etc.)
|
Let you enter a list of possible values. You can list up to
65535 values in an ENUM list. If a value is inserted that is not in the list,
a blank value will be inserted.
Note: The values are sorted in the order you enter them.
You enter the possible values in this format: ENUM('X','Y','Z')
|
SET
|
Similar to ENUM except that SET may contain up to 64 list items
and can store more than one choice
|
Number types:
Data type
|
Description
|
TINYINT(size)
|
-128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of
digits may be specified in parenthesis
|
SMALLINT(size)
|
-32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number
of digits may be specified in parenthesis
|
MEDIUMINT(size)
|
-8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum
number of digits may be specified in parenthesis
|
INT(size)
|
-2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The
maximum number of digits may be specified in parenthesis
|
BIGINT(size)
|
-9223372036854775808 to 9223372036854775807 normal. 0 to
18446744073709551615 UNSIGNED*. The maximum number of digits may be specified
in parenthesis
|
FLOAT(size,d)
|
A small number with a floating decimal point. The maximum number
of digits may be specified in the size parameter. The maximum number of
digits to the right of the decimal point is specified in the d parameter
|
DOUBLE(size,d)
|
A large number with a floating decimal point. The maximum number
of digits may be specified in the size parameter. The maximum number of
digits to the right of the decimal point is specified in the d parameter
|
DECIMAL(size,d)
|
A DOUBLE stored as a string , allowing for a fixed decimal
point. The maximum number of digits may be specified in the size parameter.
The maximum number of digits to the right of the decimal point is specified
in the d parameter
|
*The integer types have an extra option called UNSIGNED. Normally,
the integer goes from an negative to positive value. Adding the UNSIGNED
attribute will move that range up so it starts at zero instead of a negative
number.
Date types:
Data type
|
Description
|
DATE()
|
A date. Format: YYYY-MM-DD
Note: The supported range is from '1000-01-01' to '9999-12-31'
|
DATETIME()
|
*A date and time combination. Format: YYYY-MM-DD HH:MM:SS
Note: The supported range is from '1000-01-01 00:00:00' to
'9999-12-31 23:59:59'
|
TIMESTAMP()
|
*A timestamp. TIMESTAMP values are stored as the number of
seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD
HH:MM:SS
Note: The supported range is from '1970-01-01 00:00:01' UTC to
'2038-01-09 03:14:07' UTC
|
TIME()
|
A time. Format: HH:MM:SS
Note: The supported range is from '-838:59:59' to '838:59:59'
|
YEAR()
|
A year in two-digit or four-digit format.
Note: Values allowed in four-digit format: 1901 to 2155. Values
allowed in two-digit format: 70 to 69, representing years from 1970 to 2069
|
*Even if DATETIME and TIMESTAMP return the same format, they work
very differently. In an INSERT or UPDATE query, the TIMESTAMP automatically set
itself to the current date and time. TIMESTAMP also accepts various formats,
like YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD.
SQL Server Data Types
Character strings:
Data type
|
Description
|
Storage
|
char(n)
|
Fixed-length character string. Maximum 8,000 characters
|
n
|
varchar(n)
|
Variable-length character string. Maximum 8,000 characters
|
|
varchar(max)
|
Variable-length character string. Maximum 1,073,741,824
characters
|
|
text
|
Variable-length character string. Maximum 2GB of text data
|
Unicode strings:
Data type
|
Description
|
Storage
|
nchar(n)
|
Fixed-length Unicode data. Maximum 4,000 characters
|
|
nvarchar(n)
|
Variable-length Unicode data. Maximum 4,000 characters
|
|
nvarchar(max)
|
Variable-length Unicode data. Maximum 536,870,912 characters
|
|
ntext
|
Variable-length Unicode data. Maximum 2GB of text data
|
Binary types:
Data type
|
Description
|
Storage
|
bit
|
Allows 0, 1, or NULL
|
|
binary(n)
|
Fixed-length binary data. Maximum 8,000 bytes
|
|
varbinary(n)
|
Variable-length binary data. Maximum 8,000 bytes
|
|
varbinary(max)
|
Variable-length binary data. Maximum 2GB
|
|
image
|
Variable-length binary data. Maximum 2GB
|
Number types:
Data type
|
Description
|
Storage
|
tinyint
|
Allows whole numbers from 0 to 255
|
1 byte
|
smallint
|
Allows whole numbers between -32,768 and 32,767
|
2 bytes
|
int
|
Allows whole numbers between -2,147,483,648 and 2,147,483,647
|
4 bytes
|
bigint
|
Allows whole numbers between -9,223,372,036,854,775,808 and
9,223,372,036,854,775,807
|
8 bytes
|
decimal(p,s)
|
Fixed precision and scale numbers.
Allows numbers from -10^38 +1 to 10^38 –1.
The p parameter indicates the maximum total number of digits
that can be stored (both to the left and to the right of the decimal point).
p must be a value from 1 to 38. Default is 18.
The s parameter indicates the maximum number of digits stored to
the right of the decimal point. s must be a value from 0 to p. Default value
is 0
|
5-17 bytes
|
numeric(p,s)
|
Fixed precision and scale numbers.
Allows numbers from -10^38 +1 to 10^38 –1.
The p parameter indicates the maximum total number of digits
that can be stored (both to the left and to the right of the decimal point).
p must be a value from 1 to 38. Default is 18.
The s parameter indicates the maximum number of digits stored to
the right of the decimal point. s must be a value from 0 to p. Default value
is 0
|
5-17 bytes
|
smallmoney
|
Monetary data from -214,748.3648 to 214,748.3647
|
4 bytes
|
money
|
Monetary data from -922,337,203,685,477.5808 to
922,337,203,685,477.5807
|
8 bytes
|
float(n)
|
Floating precision number data from -1.79E + 308 to 1.79E + 308.
The n parameter indicates whether the field should hold 4 or 8
bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field.
Default value of n is 53.
|
4 or 8 bytes
|
real
|
Floating precision number data from -3.40E + 38 to 3.40E + 38
|
4 bytes
|
Date types:
Data type
|
Description
|
Storage
|
datetime
|
From January 1, 1753 to December 31, 9999 with an accuracy of
3.33 milliseconds
|
8 bytes
|
datetime2
|
From January 1, 0001 to December 31, 9999 with an accuracy of
100 nanoseconds
|
6-8 bytes
|
smalldatetime
|
From January 1, 1900 to June 6, 2079 with an accuracy of 1
minute
|
4 bytes
|
date
|
Store a date only. From January 1, 0001 to December 31, 9999
|
3 bytes
|
time
|
Store a time only to an accuracy of 100 nanoseconds
|
3-5 bytes
|
datetimeoffset
|
The same as datetime2 with the addition of a time zone offset
|
8-10 bytes
|
timestamp
|
Stores a unique number that gets updated every time a row gets
created or modified. The timestamp value is based upon an internal clock and
does not correspond to real time. Each table may have only one timestamp
variable
|
Other data types:
Data type
|
Description
|
sql_variant
|
Stores up to 8,000 bytes of data of various data types, except
text, ntext, and timestamp
|
uniqueidentifier
|
Stores a globally unique identifier (GUID)
|
xml
|
Stores XML formatted data. Maximum 2GB
|
cursor
|
Stores a reference to a cursor used for database operations
|
table
|
Stores a result-set for later processing
|
SQL Functions
SQL has many built-in functions for performing calculations on
data.
SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from
values in a column.
Useful aggregate functions:
- AVG() - Returns the average value
- COUNT() - Returns the number of rows
- FIRST() - Returns the first value
- LAST() - Returns the last value
- MAX() - Returns the largest value
- MIN() - Returns the smallest value
- SUM() - Returns the sum
SQL Scalar functions
SQL scalar functions return a single value, based on the input
value.
Useful scalar functions:
- UCASE() - Converts a field to upper case
- LCASE() - Converts a field to lower case
- MID() - Extract characters from a text field
- LEN() - Returns the length of a text field
- ROUND() - Rounds a numeric field to the number of decimals specified
- NOW() - Returns the current system date and time
- FORMAT() - Formats how a field is to be displayed
Tip: The aggregate functions and the scalar functions will be
explained in details in the next chapters.
The AVG() Function
The AVG() function returns the average value of a numeric column.
SQL AVG() Syntax
SELECT AVG(column_name) FROM table_name
SQL AVG() Example
We have the following "Orders" table:
O_Id
|
OrderDate
|
OrderPrice
|
Customer
|
1
|
2008/11/12
|
1000
|
Hansen
|
2
|
2008/10/23
|
1600
|
Nilsen
|
3
|
2008/09/02
|
700
|
Hansen
|
4
|
2008/09/03
|
300
|
Hansen
|
5
|
2008/08/30
|
2000
|
Jensen
|
6
|
2008/10/04
|
100
|
Nilsen
|
Now we want to find the average value of the
"OrderPrice" fields.
We use the following SQL statement:
SELECT AVG(OrderPrice) AS OrderAverage FROM
Orders
The result-set will look like this:
OrderAverage
|
950
|
Now we want to find the customers that have an OrderPrice value
higher than the average OrderPrice value.
We use the following SQL statement:
SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
The result-set will look like this:
Customer
|
Hansen
|
Nilsen
|
Jensen
|
SQL COUNT() Function
The COUNT() function returns the number of rows that matches a
specified criteria.
SQL COUNT(column_name) Syntax
The COUNT(column_name) function returns the number of values (NULL
values will not be counted) of the specified column:
SELECT COUNT(column_name) FROM table_name
SQL COUNT(*) Syntax
The COUNT(*) function returns the number of records in a table:
SELECT COUNT(*) FROM table_name
SQL COUNT(DISTINCT column_name) Syntax
The COUNT(DISTINCT column_name) function returns the number of
distinct values of the specified column:
SELECT COUNT(DISTINCT column_name) FROM
table_name
Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server,
but not with Microsoft Access.
SQL COUNT(column_name) Example
We have the following "Orders" table:
O_Id
|
OrderDate
|
OrderPrice
|
Customer
|
1
|
2008/11/12
|
1000
|
Hansen
|
2
|
2008/10/23
|
1600
|
Nilsen
|
3
|
2008/09/02
|
700
|
Hansen
|
4
|
2008/09/03
|
300
|
Hansen
|
5
|
2008/08/30
|
2000
|
Jensen
|
6
|
2008/10/04
|
100
|
Nilsen
|
Now we want to count the number of orders from "Customer
Nilsen".
We use the following SQL statement:
SELECT COUNT(Customer) AS CustomerNilsen FROM
Orders
WHERE Customer='Nilsen'
WHERE Customer='Nilsen'
The result of the SQL statement above will be 2, because the
customer Nilsen has made 2 orders in total:
CustomerNilsen
|
2
|
SQL COUNT(*) Example
If we omit the WHERE clause, like this:
SELECT COUNT(*) AS NumberOfOrders FROM Orders
The result-set will look like this:
NumberOfOrders
|
6
|
which is the total number of rows in the table.
SQL COUNT(DISTINCT column_name) Example
Now we want to count the number of unique customers in the
"Orders" table.
We use the following SQL statement:
SELECT COUNT(DISTINCT Customer) AS
NumberOfCustomers FROM Orders
The result-set will look like this:
NumberOfCustomers
|
3
|
which is the number of unique customers (Hansen, Nilsen, and
Jensen) in the "Orders" table.
The FIRST() Function
The FIRST() function returns the first value of the selected
column.
SQL FIRST() Syntax
SELECT FIRST(column_name) FROM table_name
SQL FIRST() Example
We have the following "Orders" table:
O_Id
|
OrderDate
|
OrderPrice
|
Customer
|
1
|
2008/11/12
|
1000
|
Hansen
|
2
|
2008/10/23
|
1600
|
Nilsen
|
3
|
2008/09/02
|
700
|
Hansen
|
4
|
2008/09/03
|
300
|
Hansen
|
5
|
2008/08/30
|
2000
|
Jensen
|
6
|
2008/10/04
|
100
|
Nilsen
|
Now we want to find the first value of the "OrderPrice"
column.
We use the following SQL statement:
SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM
Orders
Tip: Workaround if FIRST() function is not
supported:
SELECT OrderPrice FROM Orders ORDER BY O_Id
LIMIT 1
The result-set will look like this:
FirstOrderPrice
|
1000
|
The LAST() Function
The LAST() function returns the last value of the selected column.
SQL LAST() Syntax
SELECT LAST(column_name) FROM table_name
SQL LAST() Example
We have the following "Orders" table:
O_Id
|
OrderDate
|
OrderPrice
|
Customer
|
1
|
2008/11/12
|
1000
|
Hansen
|
2
|
2008/10/23
|
1600
|
Nilsen
|
3
|
2008/09/02
|
700
|
Hansen
|
4
|
2008/09/03
|
300
|
Hansen
|
5
|
2008/08/30
|
2000
|
Jensen
|
6
|
2008/10/04
|
100
|
Nilsen
|
Now we want to find the last value of the "OrderPrice"
column.
We use the following SQL statement:
SELECT LAST(OrderPrice) AS LastOrderPrice FROM
Orders
Tip: Workaround if LAST() function is not
supported:
SELECT OrderPrice FROM Orders ORDER BY O_Id DESC
LIMIT 1
The result-set will look like this:
LastOrderPrice
|
100
|
The MAX() Function
The MAX() function returns the largest value of the selected
column.
SQL MAX() Syntax
SELECT MAX(column_name) FROM table_name
SQL MAX() Example
We have the following "Orders" table:
O_Id
|
OrderDate
|
OrderPrice
|
Customer
|
1
|
2008/11/12
|
1000
|
Hansen
|
2
|
2008/10/23
|
1600
|
Nilsen
|
3
|
2008/09/02
|
700
|
Hansen
|
4
|
2008/09/03
|
300
|
Hansen
|
5
|
2008/08/30
|
2000
|
Jensen
|
6
|
2008/10/04
|
100
|
Nilsen
|
Now we want to find the largest value of the
"OrderPrice" column.
We use the following SQL statement:
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM
Orders
The result-set will look like this:
LargestOrderPrice
|
2000
|
The MIN() Function
The MIN() function returns the smallest value of the selected
column.
SQL MIN() Syntax
SELECT MIN(column_name) FROM table_name
SQL MIN() Example
We have the following "Orders" table:
O_Id
|
OrderDate
|
OrderPrice
|
Customer
|
1
|
2008/11/12
|
1000
|
Hansen
|
2
|
2008/10/23
|
1600
|
Nilsen
|
3
|
2008/09/02
|
700
|
Hansen
|
4
|
2008/09/03
|
300
|
Hansen
|
5
|
2008/08/30
|
2000
|
Jensen
|
6
|
2008/10/04
|
100
|
Nilsen
|
Now we want to find the smallest value of the
"OrderPrice" column.
We use the following SQL statement:
SELECT MIN(OrderPrice) AS SmallestOrderPrice
FROM Orders
The result-set will look like this:
SmallestOrderPrice
|
100
|
The SUM() Function
The SUM() function returns the total sum of a numeric column.
SQL SUM() Syntax
SELECT SUM(column_name) FROM table_name
SQL SUM() Example
We have the following "Orders" table:
O_Id
|
OrderDate
|
OrderPrice
|
Customer
|
1
|
2008/11/12
|
1000
|
Hansen
|
2
|
2008/10/23
|
1600
|
Nilsen
|
3
|
2008/09/02
|
700
|
Hansen
|
4
|
2008/09/03
|
300
|
Hansen
|
5
|
2008/08/30
|
2000
|
Jensen
|
6
|
2008/10/04
|
100
|
Nilsen
|
Now we want to find the sum of all "OrderPrice"
fields".
We use the following SQL statement:
SELECT SUM(OrderPrice) AS OrderTotal FROM Orders
The result-set will look like this:
OrderTotal
|
5700
|
The GROUP BY Statement
The GROUP BY statement is used in conjunction with the aggregate
functions to group the result-set by one or more columns.
SQL GROUP BY Syntax
SELECT column_name,
aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
FROM table_name
WHERE column_name operator value
GROUP BY column_name
SQL GROUP BY Example
We have the following "Orders" table:
O_Id
|
OrderDate
|
OrderPrice
|
Customer
|
1
|
2008/11/12
|
1000
|
Hansen
|
2
|
2008/10/23
|
1600
|
Nilsen
|
3
|
2008/09/02
|
700
|
Hansen
|
4
|
2008/09/03
|
300
|
Hansen
|
5
|
2008/08/30
|
2000
|
Jensen
|
6
|
2008/10/04
|
100
|
Nilsen
|
Now we want to find the total sum (total order) of each customer.
We will have to use the GROUP BY statement to group the customers.
We use the following SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
GROUP BY Customer
The result-set will look like this:
Customer
|
SUM(OrderPrice)
|
Hansen
|
2000
|
Nilsen
|
1700
|
Jensen
|
2000
|
Nice! Isn't it? :)
Let's see what happens if we omit the GROUP BY statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
The result-set will look like this:
Customer
|
SUM(OrderPrice)
|
Hansen
|
5700
|
Nilsen
|
5700
|
Hansen
|
5700
|
Hansen
|
5700
|
Jensen
|
5700
|
Nilsen
|
5700
|
The result-set above is not what we wanted.
Explanation of why the above SELECT statement cannot be used: The
SELECT statement above has two columns specified (Customer and SUM(OrderPrice).
The "SUM(OrderPrice)" returns a single value (that is the total sum
of the "OrderPrice" column), while "Customer" returns 6
values (one value for each row in the "Orders" table). This will
therefore not give us the correct result. However, you have seen that the GROUP
BY statement solves this problem.
GROUP BY More Than One Column
We can also use the GROUP BY statement on more than one column,
like this:
SELECT Customer,OrderDate,SUM(OrderPrice) FROM
Orders
GROUP BY Customer,OrderDate
GROUP BY Customer,OrderDate
The HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could
not be used with aggregate functions.
SQL HAVING Syntax
SELECT column_name,
aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
SQL HAVING Example
We have the following "Orders" table:
O_Id
|
OrderDate
|
OrderPrice
|
Customer
|
1
|
2008/11/12
|
1000
|
Hansen
|
2
|
2008/10/23
|
1600
|
Nilsen
|
3
|
2008/09/02
|
700
|
Hansen
|
4
|
2008/09/03
|
300
|
Hansen
|
5
|
2008/08/30
|
2000
|
Jensen
|
6
|
2008/10/04
|
100
|
Nilsen
|
Now we want to find if any of the customers have a total order of
less than 2000.
We use the following SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
The result-set will look like this:
Customer
|
SUM(OrderPrice)
|
Nilsen
|
1700
|
Now we want to find if the customers "Hansen" or
"Jensen" have a total order of more than 1500.
We add an ordinary WHERE clause to the SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Hansen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
WHERE Customer='Hansen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
The result-set will look like this:
Customer
|
SUM(OrderPrice)
|
Hansen
|
2000
|
Jensen
|
2000
|
The UCASE() Function
The UCASE() function converts the value of a field to uppercase.
SQL UCASE() Syntax
SELECT UCASE(column_name) FROM table_name
Syntax for SQL Server
SELECT UPPER(column_name) FROM table_name
SQL UCASE() Example
We have the following "Persons" table:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
Now we want to select the content of the "LastName" and
"FirstName" columns above, and convert the "LastName"
column to uppercase.
We use the following SELECT statement:
SELECT UCASE(LastName) as LastName,FirstName
FROM Persons
The result-set will look like this:
LastName
|
FirstName
|
HANSEN
|
Ola
|
SVENDSON
|
Tove
|
PETTERSEN
|
Kari
|
The LCASE() Function
The LCASE() function converts the value of a field to lowercase.
SQL LCASE() Syntax
SELECT LCASE(column_name) FROM table_name
Syntax for SQL Server
SELECT LOWER(column_name) FROM table_name
SQL LCASE() Example
We have the following "Persons" table:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
Now we want to select the content of the "LastName" and
"FirstName" columns above, and convert the "LastName"
column to lowercase.
We use the following SELECT statement:
SELECT LCASE(LastName) as LastName,FirstName
FROM Persons
The result-set will look like this:
LastName
|
FirstName
|
hansen
|
Ola
|
svendson
|
Tove
|
pettersen
|
Kari
|
The MID() Function
The MID() function is used to extract characters from a text
field.
SQL MID() Syntax
SELECT MID(column_name,start[,length]) FROM
table_name
Parameter
|
Description
|
column_name
|
Required. The field to extract characters from
|
start
|
Required. Specifies the starting position (starts at 1)
|
length
|
Optional. The number of characters to return. If omitted, the
MID() function returns the rest of the text
|
SQL MID() Example
We have the following "Persons" table:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
Now we want to extract the first four characters of the
"City" column above.
We use the following SELECT statement:
SELECT MID(City,1,4) as SmallCity FROM Persons
The result-set will look like this:
SmallCity
|
Sand
|
Sand
|
Stav
|
The LEN() Function
The LEN() function returns the length of the value in a text
field.
SQL LEN() Syntax
SELECT LEN(column_name) FROM table_name
SQL LEN() Example
We have the following "Persons" table:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
Now we want to select the length of the values in the
"Address" column above.
We use the following SELECT statement:
SELECT LEN(Address) as LengthOfAddress FROM
Persons
The result-set will look like this:
LengthOfAddress
|
12
|
9
|
9
|
The ROUND() Function
The ROUND() function is used to round a numeric field to the
number of decimals specified.
SQL ROUND() Syntax
SELECT ROUND(column_name,decimals) FROM
table_name
Parameter
|
Description
|
column_name
|
Required. The field to round.
|
decimals
|
Required. Specifies the number of decimals to be returned.
|
SQL ROUND() Example
We have the following "Products" table:
Prod_Id
|
ProductName
|
Unit
|
UnitPrice
|
1
|
Jarlsberg
|
1000 g
|
10.45
|
2
|
Mascarpone
|
1000 g
|
32.56
|
3
|
Gorgonzola
|
1000 g
|
15.67
|
Now we want to display the product name and the price rounded to
the nearest integer.
We use the following SELECT statement:
SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice
FROM Products
The result-set will look like this:
ProductName
|
UnitPrice
|
Jarlsberg
|
10
|
Mascarpone
|
33
|
Gorgonzola
|
16
|
The NOW() Function
The NOW() function returns the current system date and time.
SQL NOW() Syntax
SELECT NOW() FROM table_name
SQL NOW() Example
We have the following "Products" table:
Prod_Id
|
ProductName
|
Unit
|
UnitPrice
|
1
|
Jarlsberg
|
1000 g
|
10.45
|
2
|
Mascarpone
|
1000 g
|
32.56
|
3
|
Gorgonzola
|
1000 g
|
15.67
|
Now we want to display the products and prices per today's date.
We use the following SELECT statement:
SELECT ProductName, UnitPrice, Now() as PerDate
FROM Products
The result-set will look like this:
ProductName
|
UnitPrice
|
PerDate
|
Jarlsberg
|
10.45
|
10/7/2008 11:25:02 AM
|
Mascarpone
|
32.56
|
10/7/2008 11:25:02 AM
|
Gorgonzola
|
15.67
|
10/7/2008 11:25:02 AM
|
The FORMAT() Function
The FORMAT() function is used to format how a field is to be
displayed.
SQL FORMAT() Syntax
SELECT FORMAT(column_name,format) FROM
table_name
Parameter
|
Description
|
column_name
|
Required. The field to be formatted.
|
format
|
Required. Specifies the format.
|
SQL FORMAT() Example
We have the following "Products" table:
Prod_Id
|
ProductName
|
Unit
|
UnitPrice
|
1
|
Jarlsberg
|
1000 g
|
10.45
|
2
|
Mascarpone
|
1000 g
|
32.56
|
3
|
Gorgonzola
|
1000 g
|
15.67
|
Now we want to display the products and prices per today's date
(with today's date displayed in the following format "YYYY-MM-DD").
We use the following SELECT statement:
SELECT ProductName, UnitPrice,
FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products
FROM Products
The result-set will look like this:
ProductName
|
UnitPrice
|
PerDate
|
Jarlsberg
|
10.45
|
2008-10-07
|
Mascarpone
|
32.56
|
2008-10-07
|
Gorgonzola
|
15.67
|
2008-10-07
|
THANK YOU
No comments:
Post a Comment
Thanks for your great comment!