|
Function Name Click For An example: |
Its Use |
| CONCAT | As the name might indicates, it is used to merge, or concatenate two strings, or fields of data together. There are two versions of this function, as can be seen by clicking the function name at left. |
| UPPER | This function is used to convert text strings from any case to upper case. Its easily used to make sure of your search criteria, when you don't know how data was entered in a table, i.e. select last from customer where upper(last) = 'JONES';. because you used the upper case "jones","JONES" or "Jones" will all be returned. |
| LOWER | This function is used to convert text strings from any case to Lower case. Its easily used to make sure of your search criteria, when you don't know how data was entered in a table, i.e. select last from customer where Lower(last) = 'JONES';. because you used the upper case "jones","JONES" or "Jones" will all be returned. |
| INITCAP | This function is used to convert text strings from any case to Mixed case. Its used to make your output more appealing to the eye. It takes "JONES", "jones",or "Jones" and turns them into "Jones". |
| LENGTH | Use this function to get the length of any string. |
| SUBSTR | SUBSTR will output any part of a string you want. You have to give it the field name, starting point, and number of characters to return. |
| INSTR | This function tells you where a part of a string is located within a field. For example instr('spring','ing') would return 4, because it starts at the 4th position. |
| RTRIM | RTRIM is used to remove all the white space, or blank spaces from the right of a string RTRIM(" Quick Brown Fox ". Returns " Quick Brown Fox". |
| LTRIM | LTRIM is used to remove all the white space, or blank spaces from the left of a string LTRIM(" Quick Brown Fox ". Returns "Quick Brown Fox ". |
| RPAD | RPAD will Left justify your output. You must tell it what field to output, as well as how many characters to allow. RPAD will insert empty spaces to the right for how ever many characters aren't present to fill the space. |
| LPAD | LPAD will Right justify your output. You must tell it what field to output, as well as how many characters to allow. RPAD will insert empty spaces to the left for how ever many characters aren't present to fill the space. |
| NVL |
Use this function in your calculations when you have null values in your numeric fields. This is because NULL (* / + -) anything is null. NULL will give you some strange results in your math, avoid it at all cost. We all know that 1 + 1 = 2, But 1 + NULL = NULL. |
THE CONCAT FUNCTION
There are two methods of using the CONCAT function. One in parenthesis which is simply a two
parameter function, and one which uses double PIPE characters, which allows the addition of spacing,
and more than two strings to concatenate. Examples of both methods are listed below.
Note there is no spacing in the concatenated string after using the below function.
With this version spacing can also be added to dress up the output.
Top
THE UPPER FUNCTION
The upper function converts any string from any case to entirely upper case. Therefore, "Jones", "jones",
or even "JONES" is converted to "JONES". This is good when performing a search. In addition to the search
enhancements, it can also format your output.
SELECT FIRST,LAST FROM CUSTOMER WHERE UPPER(LAST) = 'JONES';
Will retrieve records with "Jones", "JONES", or "jones".
Top
THE LOWER FUNCTION The Lower Function performs the opposite operation from the upper function in that it converts a string to all lower case. It has the same uses in searching and formatting as the upper function. Therefore the SQL statement:
SELECT FIRST,LAST FROM CUSTOMER WHERE LOWER(LAST) = 'jones';
Will retrieve records with "Jones", "JONES", or "jones".
Because search strings are case sensitive these functions are vital, because we never know how the data was entered.
Top
THE INITCAP FUNCTION The INITCAP function is used mainly to make your output more aesthetically pleasing. It presents your data in manner where the first letter of each word in capitalized, and all other letters are in lower case. Making it easier to read.
Top
THE LENGTH FUNCTION
The LENGTH function is used to find the length of a string. Suppose you need to know the longest entry in a given field. You can use the length function to find this out. There are many uses for the length function the one given is probably not the best example but I'm sure you will find your many of your own.
Top
THE SUBSTR FUNCTION
The SUBSTR function can be used to extract part of a string. Suppose you want only the first 5 digits of a zip code. Most zip code fields are 9 or 10 characters in length. So to get the first 5 digits you could use an SQL Statement like:
SELECT SUBSTR(ZIPCODE,1,5) FROM CUSTOMER;
This will pull data from the zip code field, starting at position 1, extracting 5 positions worth of data.
Top
THE INSTR FUNCTION
The INSTR function will tell you the location of a character sequence, that you specify, within a string. I've used this many times in Access to locate carriage returns so that I can split multi-line entries into single line entries. It makes a handy variable within your own functions. You give it the field name, and what to look for and it will tell you where in the string it is located.
Top
THE RTRIM FUNCTION
The RTRIM function gets rid of the white space after the actual data in a field. For example, You have a Field called CITY which has a field length of 25 characters. Suppose also that you have Two cities, "DALTON" and "NORTH MYRTLE BEACH". Without the RTRIM function the output for
"DALTON GA 39999"
"NORTH MYRTLE BEACH SC 29999"With the RTRIM Function our output would be:
"DALTON GA 39999"
"NORTH MYRTLE BEACH SC 29999"
Which is how it should look.
Top
THE LTRIM FUNCTION
The LTRIM function gets rid of the white space before the actual data in a field. If for whatever reason your data is right justified, LTRIM will work the same as does RTRIM above, Just on the other side of the data.
You can also combine both LTRIM and RTRIM to remove spaces on both sides of the data. Such as:
LTRIM(RTRIM(NAME)) would convert " Bill " to
Just "Bill"
Top
THE RPAD FUNCTION
The RPAD function will left Justify your output. You must provide the Field name and the amount of space to allocate for the column. RPAD will add spaces to the right in order to fill the void between data and column width.
Notice the query below:
Top
THE LPAD FUNCTION
The LPAD function will Right Justify your output. Works the same as RPAD above. It requires the same input as RPAD. LPAD will add spaces to the left of data in order to fill the void between data and the column width specified by the user.
Notice the query below:
Top
THE NVL FUNCTION
The NVL function will convert NULL values to a value specified by the user. When performing calculations on data, a NULL can cause some strange results. Say for example your payroll database contained the fields "Hourly_Pay","Hours_Worked", and "Commission". Suppose your pay is $19.00 Per Hour, and you put in a 40 hour week. However you don't get a commission, Your not in sales, so no data is entered. The Commission field is NULL. For brevity we wont concert ourselves with taxes (Wish Wish Wish).
The formula for calculating pay is Hourly_Pay * Hours_Worked + Commission.
$19.00 * 40 = $760.00 + commission, Your pay should be $760.00". But because the commission field is Null, You get
$19.00 * 40 = $760.00 + 0
You will get the right answer. The syntax is NVL(FieldName,Value), where Fieldname is the field you want to operate on, and Value is the value you want to replace it with if it is null.
Notice the query below:
Top