 |
|
EnterpriseDB: Text Functions
Oracle Tips by
Burleson
|
ASCII: Return
the ASCII code of a character:
* Parameter 1
= TEXT
* Returns = NUMBER
CHR: Return
the character with an ASCII value:
* Parameter 1
= NUMBER
* Returns = TEXT
SELECT
ascii('a'), chr(97) FROM DUAL;
edb=# SELECT ascii('a'), chr(97) FROM DUAL;
ascii | chr
-------+-----
97 | a
(1 row)
edb=#
INITCAP:
Convert the first character of each word in a string to upper case:
* Parameter 1
= VARCHAR2
* Returns = VARCHAR2
LOWER: Return
a string in lower case:
* Parameter 1
= VARCHAR2
* Returns = VARCHAR2
UPPER: Return
a string in upper case:
* Parameter 1
= VARCHAR2
* Returns = VARCHAR2
SELECT
initcap('lewis r cunningham') ic_name,
lower('Hello WORLD') low_string,
upper('Hello WORLD') up_string
FROM DUAL;
edb=# SELECT
initcap('lewis r cunningham') ic_name,
edb-# lower('Hello WORLD') low_string,
edb-# upper('Hello WORLD') up_string
edb-# FROM DUAL;
ic_name | low_string | up_string
--------------------+-------------+-------------
Lewis R Cunningham | hello world | HELLO WORLD
(1 row)
INSTR: Return
the location of a substring in a string:
* Parameter 1
= VARCHAR2, the String
* Parameter 2 = VARCHAR2, the Substring to find
* Parameter 3 = NUMBER, Optional, start location, negative starts from
the end of the string and works backwards
* Parameter 4 = NUMBER, optional occurrence, first (1), second (2),
etc
* Returns = NUMBER
SELECT
instr('abcdefabcdefabcdef', 'abc', 1, 1) p1,
instr('abcdefabcdefabcdef', 'abc', 1, 2) p2,
instr('abcdefabcdefabcdef', 'abc', 1, 3) p3,
instr('abcdefabcdefabcdef', 'abc') p4,
instr('abcdefabcdefabcdef', 'abc', -1, 1) n1,
instr('abcdefabcdefabcdef', 'abc', -1, 2) n2,
instr('abcdefabcdefabcdef', 'abc', -1, 3) n2
FROM DUAL;
edb=# SELECT
instr('abcdefabcdefabcdef', 'abc', 1, 1) p1,
edb-# instr('abcdefabcdefabcdef', 'abc', 1, 2) p2,
edb-# instr('abcdefabcdefabcdef', 'abc', 1, 3) p3,
edb-# instr('abcdefabcdefabcdef', 'abc') p4,
edb-# instr('abcdefabcdefabcdef', 'abc', -1, 1) n1,
edb-# instr('abcdefabcdefabcdef', 'abc', -1, 2) n2,
edb-# instr('abcdefabcdefabcdef', 'abc', -1, 3) n2
edb-# FROM DUAL;
p1 | p2 |
p3 | p4 | n1 | n2 | n2
----+----+----+----+----+----+----
1 | 7 | 13 | 1 | 13 | 7 | 1
(1 row)
REPLACE:
Return a string with a substring replaced:
* Parameter 1
= VARCHAR2, the String
* Parameter 2 = VARCHAR2, the Substring to find
* Parameter 3 = VARCHAR2, Optional, replacement string. Can be null
* Returns = TEXT
SELECT
replace('abcdefabcdefabcdef', 'abc', 'xyz' ) r1,
replace('abcdefabcdefabcdef', 'xyz' ) r2,
replace('abcdefabcdefabcdef', 'abc' ) r3
FROM DUAL;
edb=# SELECT
replace('abcdefabcdefabcdef', 'abc', 'xyz' ) r1,
edb-# replace('abcdefabcdefabcdef', 'xyz' ) r2,
edb-# replace('abcdefabcdefabcdef', 'abc' ) r3
edb-# FROM DUAL;
r1 | r2 | r3
--------------------+--------------------+-----------
xyzdefxyzdefxyzdef | abcdefabcdefabcdef | defdefdef
(1 row)
SUBSTR:
Return a substring from a string:
* Parameter 1
= VARCHAR2, The String
* Parameter 2 = NUMBER, Position to start with
* Parameter 3 = NUMBER, Optional, length to cut out, default to
remainder of string
* Returns = VARCHAR2
SELECT
substr('abcdefghijklmno', 4, 3) s1,
substr('abcdefghijklmno', 4 ) s2,
substr('abcdefghijklmno', -4, 3) s3,
substr('abcdefghijklmno', -4 ) s4
FROM DUAL;
edb=# SELECT
substr('abcdefghijklmno', 4, 3) s1,
edb-# substr('abcdefghijklmno', 4 ) s2,
edb-# substr('abcdefghijklmno', -4, 3) s3,
edb-# substr('abcdefghijklmno', -4 ) s4
edb-# FROM DUAL;
s1 |
s2 | s3 | s4
-----+--------------+-----+------
def | defghijklmno | lmn | lmno
(1 row)
LENGTH:
Return the length of the string
* Parameter 1
= VARCHAR2
* Returns = NUMBER
LPAD: Return
a string left padded to the defined amount:
* Parameter 1
= VARCHAR2, string to be padded
* Parameter 1 = NUMBER, length to pad to
* Parameter 1 = VARCHAR2, optional, String to be used as padding,
default is
spaces
* Returns = VARCHAR2
RPAD: Return
a string right padded to the defined amount:
* Parameter 1
= VARCHAR2, string to be padded
* Parameter 1 = NUMBER, length to pad to
* Parameter 1 = VARCHAR2, optional, String to be used as padding,
default is spaces
* Returns = VARCHAR2
SELECT
length('abcdef') len1,
lpad('abcdef', 10, 'u') lpad1,
rpad('abcdef', 10, 'z') rpad1,
lpad(rpad('abcdef', 10, 'z'), 15, 'u') combo1,
length(lpad(rpad('abcdef', 10, 'z'), 15, 'u')) len_combo1
FROM DUAL;
edb=# SELECT
length('abcdef') len1,
edb-# lpad('abcdef', 10, 'u') lpad1,
edb-# rpad('abcdef', 10, 'z') rpad1,
edb-# lpad(rpad('abcdef', 10, 'z'), 15, 'u') combo1,
edb-# length(lpad(rpad('abcdef', 10, 'z'), 15, 'u')) len_combo1
edb-# FROM DUAL;
len1 |
lpad1 | rpad1 | combo1 | len_combo1
------+------------+------------+-----------------+------------
6 | uuuuabcdef | abcdefzzzz | uuuuuabcdefzzzz | 15
(1 row)
LTRIM: Trim
spaces from the left of a string
* Parameter 1
= String to be trimmed
* Returns = VARCHAR2
RTRIM: Trim
spaces from the right of a string
* Parameter 1
= String to be trimmed
* Returns = VARCHAR2
SELECT
length(' abcdef ') len1,
ltrim(' abcdef ') ltrim1,
length(ltrim(' abcdef ')) len_ltrim1,
rtrim(' abcdef ') rtrim1,
length(rtrim(' abcdef ')) len_rtrim1,
ltrim(rtrim(' abcdef ')) alltrim1,
length(ltrim(rtrim(' abcdef '))) len_alltrim1
FROM DUAL;
edb=# SELECT
length(' abcdef ') len1,
edb-# ltrim(' abcdef ') ltrim1,
edb-# length(ltrim(' abcdef ')) len_ltrim1,
edb-# rtrim(' abcdef ') rtrim1,
edb-# length(rtrim(' abcdef ')) len_rtrim1,
edb-# ltrim(rtrim(' abcdef ')) alltrim1,
edb-# length(ltrim(rtrim(' abcdef '))) len_alltrim1
edb-# FROM DUAL;
len1 |
ltrim1 | len_ltrim1 | rtrim1 | len_rtrim1 | alltrim1 |
len_alltrim1
------+-----------+------------+-----------+------------+----------+-------------
12 | abcdef | 9 | abcdef | 9 | abcdef
| 6
(1 row)
Date Functions
CURRENT_DATE:
Returns the current date:
* Returns =
DATE
CURRENT_TIMESTAMP: Return the current timestamp:
* Returns =
TIMESTAMP
LOCALTIMESTAMP:
Return the current timestamp:
* Returns =
TIMESTAMP
SYSDATE:
Return the current timestamp:
* Returns =
TIMESTAMP
SELECT
current_date,
current_timestamp,
localtimestamp,
sysdate
FROM DUAL;
edb=# SELECT
current_date,
edb-# current_timestamp,
edb-# localtimestamp,
edb-# sysdate
edb-# FROM DUAL;
date
| now | timestamp |
timestamp
-----------+---------------------------+---------------------------+-------------11-NOV-06
| 11-NOV-06 16:30:34.843299 | 11-NOV-06 16:30:34.843299 | 11-NOV-06
16:30:35
(1 row)
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
