SQL LEN() Function

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 ...

Read More

SQL ROUND() Function

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 ...

Read More

SQL NOW() Function

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...

Read More

SQL FORMAT() Function

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 ...

Read More

SQL Quick Reference

SQL Statement Syntax AND / OR SELECT column_name(s) FROM table_name WHERE condition AND|OR condition ALTER TABLE ALTER TABLE table_name ADD column_name datatypeor ALTER TABLE table_name DROP COLUMN column_name AS (alias) SELECT column_name AS column_alias FROM table_nameor SELECT column_name FROM table_name  AS table_alias BETWEEN SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 CREATE DATABASE CREATE DATABASE...

Read More

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...

Read More

SQL NULL Functions

SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions Look at the following "Products" table: P_Id ProductName UnitPrice UnitsInStock UnitsOnOrder 1 Jarlsberg 10.45 16 15 2 Mascarpone 32.56 23   3 Gorgonzola 15.67 9 20 Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values. We have the following SELECT statement: SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder) FROM Products In the example above, if...

Read More

SQL NULL Values

NULL values represent missing unknown data. By default, a table column can hold NULL values. This chapter will explain the IS NULL and IS NOT NULL operators. SQL NULL Values If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value. NULL values are treated differently from other values. NULL is used as a placeholder...

Read More

SQL Date Functions

SQL Dates The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database. As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets complicated. Before talking about the complications of querying for dates, we will look at the most important built-in functions...

Read More