Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter