Supercharging Your SQL Statements

Structured Query Language (SQL) is a database computer language designed for managing data within relational databases. The most common applications for SQL are actions (calls) on the database using SELECT, UPDATE, DELETE and INSERT (data) functions.  SQL can provide many other database calls that will contribute to the reduction of user programs.   SQL includes built-in functionality that can help format and calculate data during any of these database actions/calls.  Below are some examples of using SQL functions to accomplish these processes.

Example 1:  You are required to select a field and compare the value from that field with a second data string.  If the selected field is larger than the data inside that field then you risk leading and/or trailing spaces and not making a match during the comparison (as the value [data] is different from [      data], and is also different from [data     ]).  Removing the spaces will make it possible to accurately compare the two values.  The TRIM function used in the SELECT statement will remove leading and trailing spaces from the selected field value.

Sample 1:  SELECT TRIM(FIELD) from TABLE

Example 2:  You are storing a sequential (next) number in a field and you need to increment that number and update the field with the new (incremented) number. You would execute a SELECT statement to get the current value, add ‘1’ to that value, and then execute an UPDATE SQL statement to update the field with this new (incremented) number.  This statement uses a select sub-query to obtain the number from the field and then uses the formula to add ‘1’ to the value.  The result is used as the new value in the update call.

Sample 2:  UPDATE TABLE1 SET FIELD_COUNTER = (SELECT FIELD_COUNTER FROM TABLE1) +1

Example 3:  You must retrieve and assemble a zip code that resides in two fields (ZIP1 is five digits; ZIP2 is four digits), and then apply the format of XXXXX-XXXX (includes inserting the hyphen between the two data sources).  This would be done by first, selecting the two fields and concatenating a hyphen to the end of ZIP1, second, storing this new value into a temporary user-field, and third, concatenating ZIP2 to the end of the value in the temporary stored user-field.  This field now contains the zip code in the required format.  This method will require one SQL “call” and one (or more) additional steps to concatenate the three data parts for the required format.  Sample 1 illustrates one SQL statement to select and format the zip code.  ZIP1 and ZIP2 are selected and trimmed of blank spaces.  The inner CONCAT joins ZIP1 with the hyphen and the outer CONCAT joins ZIP2 to the results of the first CONCAT.

Sample 3SELECT CONCAT(CONCAT(TRIM(ZIP1),’-‘), TRIM(ZIP2)) from TABLE where FIELD = VALUE

The examples above represent just three of many potential uses/functions of SQL.  Applying SQL actions/calls will simplify any user application and reduce the amount of external processes necessary to maintain special formatting within the database.

One thought on “Supercharging Your SQL Statements

  1. Pingback: SQL: The Non-standard Standard | EXTOL Technology Blog

Leave a Reply

Your email address will not be published. Required fields are marked *


*