PL/SQL – Character/String Functions

Hi Folks,

 

Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format of their arguments. This format enables them to operate on zero, one, two, or more arguments. SQL functions are built into Oracle Database and are available for use in various appropriate SQL statements.

 

Character Functions Returning Character Values

 

11 most common character functions

SYNTAX DESCRIPTION
LOWER(char) LOWER returns char, with all letters lowercase.
UPPER(char) UPPER returns char, with all letters uppercase.
LPAD(expr1, n, expr2) LPAD returns expr1, left-padded to length n characters with the sequence of characters in expr2.
RPAD(expr1, n, expr2) RPAD returns expr1, right-padded to length n characters with expr2, replicated as many times as necessary.
LTRIM(char) LTRIM removes from the left end of char all of the characters contained in set. If you do not specify set, it defaults to a single blank.
RTRIM(char) RTRIM removes from the right end of char all of the characters that appear in set. If you do not specify set, it defaults to a single blank.
TRIM(char) TRIM removes from both sides of char all the characters that appear in set. If you do not specify set, it defaults to a sigle blank
REPLACE(char, search_strin, replacement_string) REPLACE returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed.
SUBSTR(char, position, substring_length) The SUBSTR functions return a portion of char, beginning at character position, substring_length characters long.
TRANSLATE(expr, from_string, to_string) TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string.
SELECT LOWER('ABC') "LOWER",
     UPPER('abc') "UPPER",
     LPAD('1',5,'0') "LPAD",
     RPAD('1',5,'0') "RPAD",
     LTRIM('   XXX') "LTRIM",
     RTRIM('XXX   ') "RTRIM",
     TRIM('  XX  ') "TRIM", 
     REPLACE('ABC','C','X') "REPLACE",
     SUBSTR('abcd', 1, 2) "SUBSTR",
     TRANSLATE('A*B', '*', 'X') "TRANSLATE"
FROM DUAL;

 

 

A couple of more functions that you might use

SYNTAX DESCRIPTION
CHR(n) CHR returns the character having the binary equivalent to n as a VARCHAR2 value in either the database character set or, if you specify USING NCHAR_CS, the national character set.
CONTCAT(char1, char2) CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
INITCAP(char) INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.
REGEXP_REPLACE(source, pattern, replace) REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern.
REGEXP_SUBSTR(source, pattern, position) REGEXP_SUBSTR extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern. It is also similar to REGEXP_INSTR, but instead of returning the position of the substring, it returns the substring itself.
SOUNDEX(char) SOUNDEX returns a character string containing the phonetic representation of char.
SELECT CHR(67) "CHR", 
       CONCAT('ABC-', 'D') "CONCAT",
       INITCAP('hello') "INITCAP",
       REGEXP_REPLACE('Brazil', '(.)', ' ') "REGEXP_REPLACE",
       REGEXP_SUBSTR('http://www.oracle.com/products', 'http://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR", 
       SOUNDEX('Oracle') "SOUNDEX"

FROM DUAL;

blank

 

 

Character Functions Returning Number Values

Character functions that return number values can take as their argument any character datatype.

The character functions that return number values are:

SYNTAX DESCRIPTION
ASCII(char) ASCII returns the decimal representation in the database character set of the first character of char.
INSTR(string, substring) The INSTR functions search string for substring.
LENGTH(char) The LENGTH functions return the length of char.
REGEXP_INSTR(source, pattern, position) REGEXP_INSTR extends the functionality of the INSTR function by letting you search a string for a regular expression pattern.
SELECT ASCII('a') "ASCII",
       INSTR('Hello Word', 'W') "INSTR",
       LENGTH('Hello') "LENGTH",
       REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[^ ]+', 1, 6) "REGEXP_INSTR"
FROM DUAL;

blank

 

 

Please write down in the comments any further questions you may have. I’ll be happy to help you.

 

 384 views last month,  2 views today

About Joel Medeiros

blank
I am an experienced Business and Data Analyst, ETL and PL/SQL with over 13 years of experience. My technical skills are focus on Oracle mainly in developing with tools such as PL/SQL, ODI and RIB. You also can check all my post right here https://ittutorial.org/author/joel-medeiros/ Please, feel free to keep in touch by e-mail or Linkedin at: joel.medeiros01@gmail.com | https://www.linkedin.com/in/joel-medeiros/

Check Also

blank

Alter System Flush Buffer Cache in Oracle

Hi, I will explain Alter System Flush Buffer Cache in Oracle in this post.  3,324 views …

Leave a Reply