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

concat ( s1, s2)

In the last chapter, we introduced concatenation using the double vertical bars (||).   The concatfunction does the same thing.  concat(s1, s2) is the same as s1||s2 where s1 and s2 are character strings.

SQL> SELECT
  2    CONCAT('The author Named ', author_last_name) Name
  3    FROM
  4      author; 

NAME
---------------------------------------------------------
The author Named jones
The author Named hester
The author Named weaton
The author Named jeckle
The author Named withers
The author Named petty
The author Named clark
The author Named mee
The author Named shagger
The author Named smith

Well, we got what we asked for, but that looks pretty bad.  We will never get that raise if we keep sending reports like that to the boss.  Lucky for us, you can nest functions. 

column Name1 format a30
column Name2 format a30 

SELECT
  INITCAP(CONCAT('The author Named ', author_last_name)) Name1,
  CONCAT(INITCAP('The author Named '),
UPPER(author_last_name)) Name2
  FROM
    author;
clear columns

NAME1                          NAME2
------------------------------ ---------------------------The Author Named Jones         The Author Named JONES
The Author Named Hester        The Author Named HESTER
The Author Named Weaton        The Author Named WEATON
The Author Named Jeckle        The Author Named JECKLE
The Author Named Withers       The Author Named WITHERS
The Author Named Petty         The Author Named PETTY
The Author Named Clark         The Author Named CLARK
The Author Named Mee           The Author Named MEE
The Author Named Shagger       The Author Named SHAGGER
The Author Named Smith         The Author Named SMITH

Notice the parentheses define the order that the functions are evaluated, from inside to outside. 

substr (s1, b, n)

The substr function is used to extract a portion of a string.  It returns the part of s1 that starts at location b and includes n characters.

SELECT
  SUBSTR('Now is the time for all good men',1,3)
FROM
  dual;

SUB
---
Now

1 row selected.

instr (s1, s2, st, t)

The instr function is similar to the substr, except instead of returning the sub string, instr returns the location of the string.  The parameters include s1, the string we are search in, s2, the string we are searching for, st is the character location to start looking, and t, which is the number of the occurrence we are looking for (the fourth occurrence, for instance).   Both st and t default to one, which will result in searching for the first occurrence starting at the beginning of the string.

SELECT
  INSTR('Now is the time for all good men',' ',1,3)
FROM
  dual; 

INSTR('NOWISTHETIMEFORALLGOODMEN','',1,3)
-----------------------------------------                                       11

1 row selected.

In the example above, I am looking for the third occurrence of the string ď Ē (a space) starting at the beginning.  The third space in the string is at character number 11.

Now for a little challenge.  Suppose that my boss wants to know what the first word of every book title is.    Donít wonder why, the boss wants it, so we will get it.  Think about the solution before looking at the answer below.

SELECT

  SUBSTR(book_title,1,(INSTR(book_title,' ',1,1)-1)) "First Word"
FROM
  book; 

First Word
----------------------------------------------------------
windows
piano
DOS
The
zero
operations
non
UNIX
pay
the
writers
managing
bears
reduce
the
oracle9i
was
cooking
never
how

20 rows selected.

Basically, I queried a substring of the book title starting at the first character, until the first space, minus one to remove the space from the results.  This type of query is actually very common on databases that are not properly normalized.  If the author names were stored in our PUBS database in one column, we would have to use this type of query to separate the first and last names when needed.

length(s1)

Sometimes we just need to know how many characters there are in a string.  The length function returns the length of s1.

SELECT
  INITCAP(author_last_name) Name,
  LENGTH(author_last_name)  Sz
FROM
  author;

NAME                         SZ
-------------------- ----------
Jones                         5
Hester                        6
Weaton                        6
Jeckle                        6
Withers                       7
Petty                         5
Clark                         5
Mee                           3
Shagger                       7
Smith                         5

lpad (s1, s, c)/rpad (s1, s, c)

When you want to pad a string, you use lpadand rpad.  Lpad pads the string s1 until it is the size s using the character c by adding character c to the left side of s1.  Rpad does the same thing but adds the character c to the right side. 

SELECT
  LPAD('Hello',10,'*') Left,
  RPAD('Hello',10,'*') Right,
  LPAD('Hello',10)     LSpace,
  RPAD('Hello ',10,'-') Dashes
from dual;

LEFT       RIGHT      LSPACE     DASHES
---------- ---------- ---------- ----------
*****Hello Hello*****      Hello Hello ----

1 row selected.

That crazy boss (in my case, Don Burleson) is at it again.  Now he wants to know the length of the authorís names.  Since it is going to the boss, we want it to look nice along with being accurate.  The authorís name is first name, a space, and last name.  As the bossís requests get more complicated (and strange), we need a method to insure that the data we provide him is correct.  I have always found it easier to focus on the data first, then the formatting.  Lastly, donít waste the effort; place it in a script so you can use it again. 

First, letís get the data.

SELECT
  INITCAP(author_first_name||' '||author_last_name),
  LENGTH(author_first_name||' '||author_last_name)
FROM
  author;
INITCAP(AUTHOR_FIRST_NAME||''||AUTHOR_LAST_NAME)
----------------------------------------------------------LENGTH(AUTHOR_FIRST_NAME||''||AUTHOR_LAST_NAME)
-----------------------------------------------
Mark Jones
                                             10
Alvis Hester
                                             12
Erin Weaton
                                             11
Pierre Jeckle
                                             13
Lester Withers
                                             14
Juan Petty
                                             10
Louis Clark
                                             11
Minnie Mee
                                             10
Dirk Shagger
                                             12
Diego Smith
                                             11
10 rows selected.

Now, letís turn the query into a script and make it pretty for the boss.  I want set the author name column to 40 characters wide and use the dot (.) or period to fill out the space to the right using rpad.

-- auth_name_length.sql
--
column c1 Heading "Author Name" format a40
column c2 Heading Size          format 999

SELECT
  RPAD(INITCAP(author_first_name||' '||author_last_name),40,'.')  c1,
  LENGTH(author_first_name||'
'||author_last_name)                c2
FROM
  author;

clear columns 

Author Name                              Size
---------------------------------------- ----
Mark Jones..............................   10
Alvis Hester............................   12
Erin Weaton.............................   11
Pierre Jeckle...........................   13
Lester Withers..........................   14
Juan Petty..............................   10
Louis Clark.............................   11
Minnie Mee..............................   10
Dirk Shagger............................   12
Diego Smith.............................   11

Now we have something that will impress the boss.  Maybe that raise is not that far fetched an idea after all!

ltrim (s1, s2)/rtrim (s1, s2)

Sometimes you need to remove characters from the beginning and/or end of a string.  Normally you are removing spaces, but you may need to remove other characters.  Ltrim removes any character in s2 from the front of s1.  Think of s2 as a list of characters rather than a word.  Rtrim does the same thing except it removes the character from the end of s1.  The string s2 defaults to a space.  If the characters in s2 are not in s1, then s1 is returned unchanged.

trim (s2 from s1)

The trim function incorporates both ltrimand rtrimin one command.  You can set trim to remove leading, trailing or both.  The default is both. The string s2 defaults to a space.  When using trim, you can only define one character to trim.  With rtrim or ltrim, you are not restricted. 

 SELECT
  LTRIM('abcdedcba', 'abc') Left,
  RTRIM('abcdedcba', 'abc') Right,
  TRIM(LEADING 'a' FROM 'abcdedcba') TRIML,
  TRIM(TRAILING 'a' FROM 'abcdedcba') TRIMR,
  TRIM(BOTH 'a' FROM 'abcdedcba') TRIMB
FROM
  dual;
 

LEFT   RIGHT  TRIML    TRIMR    TRIMB
------ ------ -------- -------- -------

dedcba abcded bcdedcba abcdedcb bcdedcb

1 row selected.

There are a number of other text functions, but they normally apply more to PL/SQL programmers and are rarely used in a SQL query. 


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.