CONCAT: - It returns char1 concatenated with char2. Both char1 and char2 can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is in the same character set as char1. Its datatype depends on the datatypes of the arguments. In concatenations of two different datatypes, Oracle returns the datatype that results in a lossless conversion.

Example: - This example uses nesting to concatenate three character strings:

SELECT CONCAT(CONCAT(last_name, '''s job category is '), job_id) "Job" FROM employees WHERE employee_id = 152;

Job

------------------------------------------------------

Hall's job category is SA_REP

INITCAP: - It 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. char can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The return value is the same datatype as char.

Example: - The following example capitalizes each word in the string: SELECT INITCAP(’the soap’) "Capitals" FROM DUAL;

Capitals

---------

The Soap

Note: This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.

LOWER: - It returns char, with all letters lowercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as char.

Example: - The following example returns a string in lowercase: SELECT LOWER(’MR. SCOTT MCMILLAN’) "Lowercase" FROM DUAL; Lowercase

--------------------

mr. scott mcmillan

LPAD: - It returns char1, left-padded to length n with the sequence of characters in char2; char2 defaults to a single blank. If char1 is longer than n, then this function returns the portion of char1 that fits in n. Both char1 and char2 can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as char1. The argument n is the total length of the return value as it is displayed on your terminal screen.

Example: - The following example left-pads a string with the characters "*" and ".":

SELECT LPAD(’Page 1’,15,’*.’) from dual;

LTRIM: - It removes characters from the left of char, with all the leftmost characters that appear in set removed; set defaults to a single blank. If char is a character literal, then you must enclose it in single quotes. Oracle begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result. Both char and set can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as char.

Example: - The following example trims all of the left-most x’s and y’s from a string:

SELECT LTRIM(’xyxXxyLAST WORD’,’xy’) "LTRIM example" FROM DUAL;

LTRIM example

------------

XxyLAST WORD

REPLACE: - It 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. If search_string is null, then char is returned. Both search_string and replacement_string, as well as char, can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as char. REPLACE lets you substitute one string for another as well as to remove character strings.

Example: - The following example replaces occurrences of "J" with "BL":

SELECT REPLACE(’JACK and JUE’,’J’,’BL’) "Changes" FROM DUAL;

Changes

--------------

BLACK and BLUE

RPAD: - It returns char1, right-padded to length n with char2, replicated as many times as necessary; char2 defaults to a single blank. If char1 is longer than n, then this function returns the portion of char1 that fits in n. Both char1 and char2 can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as char1. The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value.

Example: - The following example right-pads a name with the letters "ab" until it is 12 characters long:

SELECT RPAD(’MORRISON’,12,’ab’) "RPAD example" FROM DUAL;

RPAD example

-----------------

MORRISONabab

RTRIM: - It returns char, with all the rightmost characters that appear in set removed; setdefaults to a single blank. If char is a character literal, then you must enclose it in single quotes. RTRIM works similarly to LTRIM. Both char and set can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as char.

Example: - The following example trims the letters "xy" from the right side of a string:

SELECT RTRIM(’BROWNINGyxXxy’,’xy’) "RTRIM example" FROM DUAL;

RTRIM examp

-----------

BROWNINGyxX

SUBSTR: - The "substring" functions return a portion of string, beginning at character position, substring_length characters long. SUBSTR calculates lengths using characters as defined by the input character set. string can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as string. Floating-point numbers passed as arguments to SUBSTR are automatically converted to integers.

Example: - The following example returns several specified substrings of "ABCDEFG":

SELECT SUBSTR(’ABCDEFG’,3,4) "Substring" FROM DUAL;

Substring

---------

CDEF

SELECT SUBSTR(’ABCDEFG’,-5,4) "Substring" FROM DUAL;

Substring

---------

CDEF

TRIM: - It enables you to trim leading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, then you must enclose it in single quotes. Both trim_character and trim_source can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as trim_source.

Example: - This example trims leading and trailing zeroes from a number:

SELECT TRIM (0 FROM 0009872348900) "TRIM Example" FROM DUAL;

TRIM Example

------------

98723489

UPPER: - It returns char, with all letters uppercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as char.

Example: - The following example returns a string in uppercase:

SELECT UPPER(’Large’) "Uppercase" FROM DUAL;

Uppercase

-----

LARGE

INSTR: - The "in string" functions search string for substring. The function returns an integer indicating the position of the character in string that is the first character. INSTR calculates strings using characters as defined by the input character set. Both string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The value returned is of NUMBER datatype. The default values of both position and occurrence are 1, meaning Oracle begins searching at the first character of string for the first occurrence of substring. The return value is relative to the beginning of string, regardless of the value of position, and is expressed in characters. If the search is unsuccessful (if substring does not appear occurrence times after the position character of string), then the return value is 0.

Example: - The following example searches the string "CORPORATE FLOOR", beginning with the third character, for the string "OR". It returns the position in CORPORATE FLOOR at which the second occurrence of "OR" begins:

SELECT INSTR(’CORPORATE FLOOR’,’OR’, 3, 2) "Instring" FROM DUAL;

Instring

----------

14

LENGTH: - The "length" functions return the length of char. LENGTH calculates length using characters as defined by the input character set. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is of datatype NUMBER. If char has datatype CHAR, then the length includes all trailing blanks. If char is null, then this function returns null.

Example: - The following example uses the LENGTH function using a single-byte database character set.

SELECT LENGTH(’CANDIDE’) "Length in characters" FROM DUAL;

Length in characters

--------------------

7