BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

   
  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

   

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.