 |
|
Oracle Tips by Burleson |
Character or Text Functions
Character functions are used to modify a char or
varchar2 column. We may need to modify the column before comparing it
to another value, or we may need it in a different format that it is
stored in the database. Sometimes we do not know how he characters
are stored, so we use a character function to insure that it is
formatted the way we need it.
upper(…)/lower(…)/initcap (…)
Both the upper and lower function accepts a
character string and converts all the character either to upper case
or lower case.
SQL> SELECT
2 author_last_name
3 FROM
4 author;
AUTHOR_LAST_NAME
----------------------------------------
jones
hester
weaton
jeckle
withers
petty
clark
mee
shagger
smith
10 rows selected.
SQL> SELECT
2 UPPER(author_last_name) Name
3 FROM
4 author;
NAME
----------------------------------------
JONES
HESTER
WEATON
JECKLE
WITHERS
PETTY
CLARK
MEE
SHAGGER
SMITH
10 rows
selected.
Another character function is the initcap
function. As you can guess from the name, it capitalizes only the
first character of each word. Let’s look at an example of each type.
Notice that I formatted my output for easy reading. I set my SQL*Plus
parameters, defined my columns. Created my query and then reset my
environment. Remember, when you define a column in SQL*Plus, it stays
defined and will by applied to any column returning with that name
until you CLEAR it or exit SQL*Plus.
set pages 999
lines 90 feedback off
column c1 heading Caps format a20
column c2 heading Lower format a20
column c3 heading Best format a20
SELECT
UPPER(author_last_name) c1,
LOWER(author_last_name) c2,
INITCAP(author_last_name) c3
FROM
author;
set lines 70 feedback on
clear columns
Caps Lower Best
-------------------- --------------------
-----------------JONES jones Jones
HESTER hester Hester
WEATON weaton Weaton
JECKLE jeckle Jeckle
WITHERS withers Withers
PETTY petty Petty
CLARK clark Clark
MEE mee Mee
SHAGGER shagger Shagger
SMITH smith Smith
In these examples, I am applying the function to
a column from the query. However, these functions can be applied to
any character input.
SELECT
UPPER('This is an EXAMPLE') c1,
LOWER('This is an EXAMPLE') c2,
INITCAP('This is an EXAMPLE') c3
FROM
dual;
Caps Lower Best
-------------------- -------------------- ---------------
THIS IS AN EXAMPLE this is an example This Is An
Example
When we covered comparison operators, I said that
capitalization in a SQL query does not matter except for data. For
example, if I want to find the author names for all authors that live
in St. Louis, I need to correctly provide the data for the equal
operator.
SQL> SELECT
2 author_last_name
3 FROM
4 author
5 WHERE
6 author_city = 'St. Louis';
no rows
selected
But, I know there are authors that live in St.
Louis. It must be that the case is incorrect. Let’s try again.
SQL> SELECT
2 author_last_name
3 FROM
4 author
5 WHERE
6 UPPER(author_city) = 'ST. LOUIS';
AUTHOR_LAST_NAME
----------------------------------------
jones
hester
weaton
3 rows
selected.
OK, I found my authors. Now, my boss is always
asking for a list of authors that live in different cities. So, I
need to take this query and make a file that will ask me for a city
name and then list the authors for that city. While we are at it,
let’s get the whole name and make it look nice. One thing I have to
watch out for is that some one else may use my script and not know
that I am comparing upper case. Just to make sure, we will upper case
the variable also.
--
auth_city.sql
-- Authors from a Specific City
--
set pages 999
lines 90 feedback on
column Name
format a20
column City format a15
SELECT
INITCAP(author_first_name||' '||author_last_name) Name,
INITCAP(author_city) City
FROM
author
WHERE
UPPER(author_city) = UPPER('&city_name');
set line 70
verify on pages 999
clear columns
-- End
SQL> @auth_city.sql
Enter value for city_name: St. Louis
NAME
CITY
-------------------- ---------------
Mark Jones St. Louis
Alvis Hester St. Louis
Erin Weaton St. Louis
3 rows
selected.
Again, I used the upper function to change both
the input variable city_name and the author_city in the comparison. I
set my SQL*Plus variables, set the columns, ran the query, reset the
SQL*Plus variables back to my default and cleared the column
definitions.
Notice that I turned verify OFF to get rid of the
“old and new” lines, but I left feedback ON. That
way, if my boss asks for authors that live in a city that is not in
the database, my report tells me that no rows were returned. This is
usually better than a blank report.
Now, when my boss asks for authors from a certain
city, all I have to do is start SQL*Plus, execute my
script and give the boss the report. We are on our way to that pay
raise (we cover updates in the next chapter).
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
