Sometimes we need to manipulate the data stored in a table. For example: retrieving converted, calculated, or reformatted data. This is where calculated fields come in.

Concatenating Fields

Concatenate means joining values together (by appending them to each other) to form a single long value. The SELECT statement can help us with this task. In SQL SELECT statements, we can concatenate columns using a special operator. Depending on what DBMS we are using, this can be a plus sign (+) or two pipes (||). For example:
SELECT 'student' + 'class' from registered_students
Or we can use a function CONCAT() :
SELECT CONCAT(student, "-", class) from registered_students The above SELECT statements concatenate the following elements:

  • The name stored in the student column
  • A string containing a -
  • The registered class stored class column

We can use aliases to give the retrieved information a new column name, using as which always come behind the SELECT statement. For example:
SELECT CONCAT(student, "-", class) from registered_students as stu_info

Tip:
Aliases have other uses too. Some common uses include renaming a column if the real table column name contains illegal characters (for example, spaces), and expanding column names if the original names are either ambiguous or easily misread.

Mathematical Calcualtions

SQL is capable of performing math calculations on retrieved data, by simply using math operators between the selected columns. For example:
select prod_id,quantity, item_price, quantity*item_price as expanded_price from OrderItems where order_num = 20008
Most commonly used math operators are listed in the following pic:

Mathoperators

Functions

Before we begin to discuss how to utilize Functions, we should be aware that using SQL functions can be highly problematic.

Why is that? Because functions tend to be very DBMS specific. Very few functions are supported identically by all major DBMSs. Although all types of functionality are usually available in each DBMS, the function name or syntax can differ greatly. If you do decide to use functions, make sure you comment your code well, so that at a later date you (or another developer) will know exactly what SQL implementation you were writing to.

Most SQL implementations support the following types of functions:

  1. Text functions are used to manipulate strings of text (for example, trimming or padding values and converting values to upper and lowercase).
  2. Numeric functions are used to perform mathematical operations on numeric data (for example, returning absolute numbers and performing algebraic calculations).
  3. Date and time functions are used to manipulate date and time values and to extract specific components from these values (for example, returning differences between dates, and checking date validity).
  4. System functions return information specific to the DBMS being used (for example, returning user login information).

Text Manipulation Functions

Following table summerizes the most useful SQL functions to manipulate strings:

SOUNDEX is an algorithm that converts any string of text into an alphanumeric pattern describing the phonetic representation of that text. SOUNDEX takes into account similar sounding characters and syllables, enabling strings to be compared by how they sound rather than how they have been typed. Although SOUNDEX is not a SQL concept, most DBMSs do offer SOUNDEX support.

Here is an example using the SOUNDEX function. Suppose you have a customer’s name “Michelle Green” in the Customer table, but what if that were a typo, and the contact actually was supposed to have been “Michael Green”? Obviously, searching by the correct contact name would return no data, as shown here:
select cust_name, cust_contact from Customers where cust_contact = "Michael Green"
Now try the same search using the SOUNDEX() function to match all contact names that sound similar to Michael Green:
select cust_name, cust_contact from Customers where soundex(cust_contact) = soundex("micheal green")
In this example, the WHERE clause uses the SOUNDEX() function to convert both the cust_contact column value and the search string to their SOUNDEX values. Because Michael Green and Michelle Green sound alike, their SOUNDEX values match, and so the WHERE clause correctly filtered the desired data.

Date and Time Manipulation Functions

Date-time manipulation functions are particularly DBMS specific. Refer to your DBMS documentation for the list of the date-time manipulation functions it supports. Here is a simple example for MySQL and MariaDB: select * from Orders where year(order_date) = 2012
The example shown here extracted and used part of a date (the year). To filter by a specific month the same process could be used, specifying an AND operator and both year and month comparisons.

Numeric Manipulation Functions

Table beneath lists some of the more commonly used numeric manipulation functions:

Refer to your DBMS documentation for a list of the supported mathematical manipulation functions.

REF: SQL in 10 mins by Ben Forta

visitors