 |
|
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 |
