New Document
Computer Science
Computer Catlog
Oracle - TOC
  1   2   3   4   5   page(4/5)


Character Functions


    Many implementations of SQL provide functions to manipulate characters and strings of characters. This section covers the most common character functions. The examples in this section use the table CHARACTERS.

CHR


    CHR returns the character equivalent of the number it uses as an argument. The character it returns depends on the character set of the database. For this example the database is set to ASCII. The column CODE includes numbers.

INPUT:

SQL> SELECT CODE, CHR (CODE) FROM CHARACTERS;


CONCAT


    When you learned about operators. The || symbol splices two strings together, as does CONCAT. It works like this:

INPUT:

SQL> SELECT CONCAT (FIRSTNAME, LASTNAME) "FIRST AND LAST NAMES"
FROM CHARACTERS;

ANALYSIS:

    Quotation marks surround the multiple-word alias FIRST AND LAST NAMES. Again, it is safest to check your implementation to see if it allows multiple-word aliases.

    Also notice that even though the table looks like two separate columns, what you are seeing is one column. The first value you concatenated, FIRSTNAME, is 15 characters wide. This operation retained all the characters in the field.

INITCAP


    INITCAP capitalizes the first letter of a word and makes all other characters lowercase.

INPUT:

SQL> SELECT FIRSTNAME BEFORE, INITCAP (FIRSTNAME) AFTER FROM CHARACTERS;

LOWER and UPPER


    As you might expect, LOWER changes all the characters to lowercase; UPPER does just the reverse. The following example starts by doing a little magic with the UPDATE function (you learn more about this next week) to change one of the values to lowercase:

INPUT:

SQL> SELECT FIRSTNAME, UPPER (FIRSTNAME), LOWER (FIRSTNAME)
FROM CHARACTERS;

LPAD and RPAD


    LPAD and RPAD take a minimum of two and a maximum of three arguments. The first argument is the character string to be operated on. The second is the number of characters to pad it with, and the optional third argument is the character to pad it with. The third argument defaults to a blank, or it can be a single character or a character string. The following statement adds five pad characters, assuming that the field LASTNAME is defined as a 15-character field:

INPUT:

SQL> SELECT LASTNAME, LPAD (LASTNAME, 20,'*') FROM CHARACTERS;

ANALYSIS:

    Why were only five pad characters added? Remember that the LASTNAME column is 15 characters wide and that LASTNAME includes the blanks to the right of the characters that make up the name. Some column data types eliminate padding characters if the width of the column value is less than the total width allocated for the column. Check your implementation. Now try the right side:

INPUT:

SQL> SELECT LASTNAME, RPAD (LASTNAME, 20,'*') FROM CHARACTERS;

ANALYSIS:

    Here you see that the blanks are considered part of the field name for these operations. The next two functions come in handy in this type of situation.


LTRIM and RTRIM


    LTRIM and RTRIM take at least one and at most two arguments. The first argument, like LPAD and RPAD, is a character string. The optional second element is either a character or character string or defaults to a blank. If you use a second argument that is not a blank, these trim functions will trim that character the same way they trim the blanks in the following examples.

INPUT:

SQL> SELECT LASTNAME, RTRIM (LASTNAME) FROM CHARACTERS;

    You can make sure that the characters have been trimmed with the following statement:

INPUT:

SQL> SELECT LASTNAME, LTRIM (LASTNAME, 'C') FROM CHARACTERS;

REPLACE


    REPLACE does just that. Of its three arguments, the first is the string to be searched. The second is the search key. The last is the optional replacement string. If the third argument is left out or NULL, each occurrence of the search key on the string to be searched is removed and is not replaced with anything.

INPUT:

SQL> SELECT LASTNAME, REPLACE (LASTNAME, 'ST','**') REPLACEMENT
FROM CHARACTERS;

SUBSTR


    This three-argument function enables you to take a piece out of a target string. The first argument is the target string. The second argument is the position of the first character to be output. The third argument is the number of characters to show. If you use a negative number as the second argument, the starting point is determined by counting backwards from the end

INPUT:

SQL> SELECT FIRSTNAME, SUBSTR (FIRSTNAME, 2, 3) FROM CHARACTERS;

ANALYSIS:

    Remember the character field FIRSTNAME in this example is 15 characters long. That is why you used a -13 to start at the third character. Counting back from 15 puts you at the start of the third character, not at the start of the second.

SSN


INPUT:

SQL> SELECT * FROM SSN-TABLE;

ANALYSIS:

    Reading the results of the preceding output is difficult--Social Security numbers usually have dashes. Now try something fancy and see whether you like the results:

INPUT:

SQL> SELECT SUBSTR(SSN,1,3)||'-'||SUBSTR(SSN,4,2)||'-'||SUBSTR(SSN,6,4) SSN FROM SSN-TABLE;
NOTE: This particular use of the substr function could come in very handy with large numbers using commas such as 1,343,178,128 and in area codes and phone numbers such as 317-787-2915 using dashes.

    Here is another good use of the SUBSTR function. Suppose you are writing a report and a few columns are more than 50 characters wide. You can use the SUBSTR function to reduce the width of the columns to a more manageable size if you know the nature of the actual data.

TRANSLATE


    The function TRANSLATE takes three arguments: the target string, the FROM string, and the TO string. Elements of the target string that occur in the FROM string are translated to the corresponding element in the TO string. Notice that the function is case sensitive.

INPUT:

SQL> SELECT FIRSTNAME, TRANSLATE (FIRSTNAME
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
'NNNNNNNNNNAAAAAAAAAAAAAAAAAAAAAAAAAA)
FROM CHARACTERS;

INSTR


    To find out where in a string a particular pattern occurs, use INSTR. Its first argument is the target string. The second argument is the pattern to match. The third and forth are numbers representing where to start looking and which match to report. This example returns a number representing the first occurrence of O starting with the second character:

INPUT:

SQL> SELECT LASTNAME, INSTR (LASTNAME, 'O', 2, 1) FROM CHARACTERS;

ANALYSIS:

    The default for the third and fourth arguments is 1. If the third argument is negative, the search starts at a position determined from the end of the string, instead of from the beginning.

LENGTH


    LENGTH returns the length of its lone character argument. For example:

INPUT:

SQL> SELECT FIRSTNAME, LENGTH (RTRIM (FIRSTNAME)) FROM CHARACTERS;

ANALYSIS:

    Note the use of the RTRIM function. Otherwise, LENGTH would return 15 for every value.
  1   2   3   4   5   page(4/5)



Google