Single Row Function-Character Function

Accept one or more argument that can be column name ,expression,Variable value or User Supplied Constant and return one value for each row.

Can be used in SELECT, WHERE and ORDER BY clause and can be nested.

Type of Single Row function

1. Character  function
2. Number function
3. Date function
4. Conversion function
5. XML function
6. Collection function
7. General function
8. large object function
9. Hierarchical function
10. Null Related function
11. Environment and Identifier function
12. Encoding and Decoding function

1. Character  function

All character function accept alphanumeric value as input parameter.it can be String or character constant or column name which have data type CHAR, VARCHAR2, NCHAR,NVARCHAR2 or clob.

Character function can return  both character and numeric values.

Character function is divided into two category

1. Case Manipulation function

• LOWER
• UPPER
• INITCAP

2. Character Manipulation function

• CONCAT
• SUBSTR
• INSTR
• LPAD
• RPAD
• TRIM
• RTRIM
• LTRIM
• REPLACE
• TRANSLATE 

etc.

Oracle SQL/PLSQL : Lower function

Purpose
convert alpha character values to lowercase.

Syntax : LOWER(expr) 


Parameter

expr  can be any of the data type it can be Char, varchar2, nchar, nvarchar2, clob, nclob.
Return Data Type
return data type  is same as expr(Input parameter)

Example

select lower('ABACaaa') lower from dual;

LOWER
-------
abacaaa

Note: dual table in SYS schema accessible to all user it has only one column named dummy which data type is varchar2(1) and has value 'X'.
it is very useful table for testing all the function

Oracle SQL/PLSQL : Upper function

Purpose

Convert alpha character values to uppercase 

Syntax : UPPER(expr)

Parameter 

expr  can be any of the data type Char,varchar2, nchar, nvarchar2, clob, nclob.

 

Return Data Type 

return data type  is same as expr.

Example 

 

This example convert Yogesh to uppercase.

select upper('Yogesh') caps from dual;

CAPS
------
YOGESH

Oracle SQL/PLSQL : INITCAP function

Purpose

capitalize first letter of word and lowercase the rest of the word  

Syntax : INITCAP(expr)

Parameter 

expr  can be any of the data type Char, varchar2, nchar, nvarchar2

 

Return Data Type 

return data type  is same as expr
Note:- The function does not support clob data type directly, However we can passed it, it can implicitly convert into supported data type.

Example 

select initcap('YOGESH KUMAR YOGI') "Initial Capital" from dual;

Initial Capital
-----------------
Yogesh Kumar Yogi

  

Oracle SQL/PLSQL : Length function (Return Numeric Value)

Purpose

Return Length of input expression.  

Syntax : LENGTH(expr)

Parameter 

expr  can be any of the data type Char, varchar2, nchar, nvarchar2, clob, nclob.

 

Return Data Type 

it return numeric value.

Example 

select length('YOGESH') "length" from dual;

    length
----------
         6

Oracle SQL/PLSQL : Concat function and Concatenation(||) operator

Purpose

concatenates first value to the second value.  

Syntax : CONCAT(char1,char2)
concat return char1 concatenated with char2.

There are two method to concatenate strings one is with concat(char1,char2) function and the other is the concatenation operator(||), which is two vertical bars or pipe symbols.

Parameter 

char1 and char2 can be any of the data type Char, varchar2, nchar, nvarchar2, clob, nclob

 

Return Data Type 

In concatenations of two different data types, Oracle Database returns the data type that results in a loss-less conversion. Therefore, if one of the arguments is a LOB, then the returned value is a LOB. If one of the arguments is a national data type, then the returned value is a national data type. For example:

• CONCAT(CLOB, NCLOB) returns NCLOB
• CONCAT(NCLOB, NCHAR) returns NCLOB
• CONCAT(NCLOB, CHAR) returns NCLOB
• CONCAT(NCHAR, CLOB) returns NCLOB

Example 

  Select concat('yogesh','kumar') "concatex" from dual;

concatex 
-----------
yogeshkumar

Oracle SQL/PLSQL : LPAD function

Syntax : LPAD(expr1, padded_length[, padded_string ])

Purpose

LPAD pads left side of expr1.

LPAD add  character  into left side of expr1 according to sequence of character in padded_string which is optional, if padded_string  is not present then it add Single blank character.  

Total length of new string will be equal to padded_length for example
the output of RPAD('YOGESH',10,'*#') function is
*#*#Yogesh

Parameter 

Both expr1 and padded_string can be any of the  char, varchar2, nchar, nvarchar2, clob, nclob.

 

Return Data Type 

return varchar2 if parameters is character data type
return nvarchar2 if parameters is national character data type
return lob if parameters is lob data type

Example 

Select LPAD('yogesh','10','*#') "LPAD" from dual;

LPAD    
----------
*#*#yogesh

  

Oracle SQL/PLSQL : RPAD function

Syntax : RPAD(expr1, padded_length[, padded_string ])
Purpose

RPAD pads right side of expr1.

RPAD add  character  into right side of expr1 according to sequence of character in padded_string which is optional, if padded_string  is not present then it add Single blank character.

Total length of new String is equal to padded_length. if padded_length is smaller than length of expr1 then LPAD will truncate expr1 to the size of padded_length.

Parameter 

Both expr1 and padded_string can be any of the  char, varchar2, nchar, nvarchar2, clob, nclob.

Return Data Type 

varchar2 if parameters is character data type
nvarchar2 if parameters is national character data type
lob if parameters is lob data type

Example 

Select RPAD('yogesh','10','*#') "RPAD" from dual;

  RPAD   
----------
yogesh*#*#

Oracle SQL/PLSQL : LTRIM function

Syntax : LTRIM(char [, trim_char_set ])

Purpose

LTRIM trim or remove unwanted characters ,symbols or spaces from  left side of char.

The optional parameter trim_char_set is used to specify which character(s) to trim from the char.

Oracle database start from the first character of char and removes all character that appear in trim_char_set, this process continue until reaching a character not in trim_char_set.
for example:
 Select LTRIM('000011110000','0') from dual;
LTRIM('0
--------
11110000

if set is not specified , the string is trimmed of spaces. for example:  
Select LTRIM('        Yogesh       ') from dual; 
LTRIM('YOGESH
-------------
Yogesh 

Parameter 

Both char and set can be any of the char, varchar2, nchar, nvarchar2, clob, nclob.

 

Return Data Type 

varchar2 if parameters is character data type
nvarchar2 if parameters is national character data type
lob if parameters is lob data type

  

Oracle SQL/PLSQL : RTRIM function

Syntax : RTRIM(char [, set ])
Purpose

RTRIM trim or remove unwanted characters ,symbols or spaces from  right side of char.

the optional parameter set is used to specify which character(s) to trim from the char.

Oracle database start from the first character of char and removes all character that appear in set, this process continue until reaching a character not in set.

for example:
Select RTRIM('000Yogesh000','0') from dual;

RTRIM('00
---------
000Yogesh









if set is not specified , the string is trimmed of spaces.  

Select RTRIM('        Yogesh       ') from dual;

RTRIM('YOGESH'
--------------
        Yogesh

Parameter 

Both char and set can be any of the char, varchar2, nchar, nvarchar2, clob, nclob.

 

Return Data Type 

varchar2 if parameters is character data type
nvarchar2 if parameters is national character data type
lob if parameters is lob data type

Oracle SQL/PLSQL : Lower function

Syntax : 

TRIM([ { 

{ LEADING | TRAILING | BOTH }
         [ trim_character ]
       | trim_character
       }
       FROM 
     ]
     trim_source
    )

Purpose

TRIM remove leading or trailing or both characters from a string.

• If you specify LEADING, then Oracle Database removes any leading characters equal to trim_character. Similar to LTRIM
  for example:
   SELECT
  TRIM(leading '0' from '00001030303000000') "leading_trim"
  FROM DUAL;
  leading_trim
  -------------
  1030303000000
• If you specify TRAILING, then Oracle removes any trailing characters equal to trim_character. Similar to RTRIM
  
  SELECT
  TRIM( trailing '0' from '00001030303000000') "trailing_trim"
  FROM DUAL;
  trailing_tr
  -----------
  00001030303
• If you specify BOTH or none of the three, then Oracle removes leading and trailing characters equal to trim_character
SELECT
TRIM( both '0' from '00001030303000000') "both_trim"
FROM DUAL;
both_tr
-------
1030303
• If you specify only trim_source, then Oracle removes leading and trailing blank spaces
  for example:
  SELECT
  TRIM('  Yogesh  ') "trim"
  FROM DUAL;
  trim
  ------
  Yogesh
• If either trim_source or trim_character is null, then the TRIM function returns null.
  for example:
  SELECT
  TRIM(leading '0' from null) "trim"
  FROM DUAL;
   trim
  ----
  
  SELECT
  TRIM(leading null from '000Yogesh000') "trim"
  FROM DUAL;
  trim
  ----
     

Parameter 

Both trim_character and trim_source can be VARCHAR2 or any data type that can be implicitly converted to VARCHAR2.

Return Data Type 

The string returned is a VARCHAR2 (NVARCHAR2) data type if trim_source is a CHAR or VARCHAR2 (NCHAR or NVARCHAR2) data type, and a CLOB if trim_source is a CLOB data type. The return string is in the same character set as trim_source.

Oracle SQL/PLSQL : SUBSTR function

Syntax : SUBSTR(char,starting_position,[substring_length] )
Purpose

SUBSTR return portion of a char , beginning from starting_position up to optional substring_length

• If starting_position is 0, then it is treated as 1 and Oracle Database Start beginning of char, up to substring_length if mentioned.
  for example:
  1. this query starting_position is 0 and substring_length is 6
  select SUBSTR('yogesh kumar',0,6) "Substr_ex" from dual;
  Substr
  ------
  yogesh
  2.  this query starting_position is 1
  select SUBSTR('yogesh kumar',1,6) "Substr_ex" from dual;
  Substr
  ------
  yogesh
• If starting_position is positive, then Oracle Database counts from the beginning of char to find the first character.
  for example:
  select SUBSTR('yogesh kumar',3,7) "Substr_ex" from dual;
  Substr_
  -------
  gesh ku
• If starting_position is negative, then Oracle counts backward from the end of char.
  for example
  this query starting_position is -1 it start from end of char( i.e. yogesh kumar )
  1. select SUBSTR('yogesh kumar',-1) "Substr_ex" from dual;
  S
  -
  r
  
  2. select SUBSTR('yogesh kumar',-1,1) "Substr_ex" from dual;
  S
  -
  r
  3.  this query starting_position is -1 and substring_length is 2. -1 starting_position indicate last character from string which is 'r ' in this case and substring_length is 2 because r is last character there is no more character in this string hence query return only r.
  select SUBSTR('yogesh kumar',-1,2) "Substr_ex" from dual;
  S
  -
  r
  4.  this query starting_position is -3 which is m from end of String and substring_length is 2 so result is ma
  select SUBSTR('yogesh kumar',-3,2) "Substr_ex" from dual;
  Su
  --
  ma
• If substring_length is omitted, then Oracle returns all characters to the end of char. 
  for example
  select SUBSTR('yogesh kumar',3) "Substr_ex" from dual;
  Substr_ex
  ----------
  gesh kumar
  **If substring_length is less than 1, then Oracle returns null.
  for example
  select SUBSTR('yogesh kumar',3,0) "Substr_ex" from dual;
   Substr_ex
  ---------
          
  select SUBSTR('yogesh kumar',3,-1) "Substr_ex" from dual;
  Substr_ex
  ---------

Parameter 

char can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

 

Return Data Type 

The return value is the same data type as char

Oracle SQL/PLSQL : INSTR function(Return Numeric Value)

Syntax : INSTR(source_string,search_substring,[starting_position],[occurrence])

Purpose

INSTR return position of search_substring founded in source_string  beginning from starting_position and return found position according to occurrence. 

•   if starting_position and  occurrence is omitted then first match search_substring position is return. for example:  
  SELECT INSTR('mnA1pqA1rsA1','A1') "instrex" from dual;
  instrex
  ----------
           3
  
  
•  if  starting_position and occurrence is mentioned then search begin form starting_position and according ocurrence position of search_substring will be returned. for example
  SELECT INSTR('mnA1pqA1rsA1','A1',1,2) "instrex" from dual;
  instrex
  ----------
           7

Parameter 

search_string can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.  

search_substring can be any of the data types  CHAR,  VARCHAR2,  NCHAR, NVARCHAR2,  CLOB,  or NCLOB.

Both starting_position and occurrence must be of NUMBER, or any data type that can be implicitly converted to NUMBER, and must resolve to an integer. The default values of both starting_position and occurrence are 1.

 

Return Data Type 

The value returned is of NUMBER data type.

Oracle SQL/PLSQL : Replace function

Syntax : REPLACE(char, search_string [, replacement_string ] )
Purpose

REPLACE return char , Replace function first find search_string in char and then if replacement_string is available it replace search_string with replacement_String and return new char.
for example:
 SELECT REPLACE('yogar Kumar','ar','PI') from dual;
REPLACE('YO
-----------
yogPI KumPI

if replacement_String is not present then it remove search_string from char and return new char.
for example:
SELECT REPLACE('yogar Kumar','ar') from dual;

REPLACE(
--------
yogh Kum

Parameter 

Both search_string and replacement_string, as well as char, can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. 

 

Return Data Type 

The string returned is in the same character set as char.

Oracle SQL/PLSQL : Translate function

Syntax : TRANSLATE(expr, from_string, to_string)
Purpose

TRANSLATE provides single-character, one-to-one substitution. Translate function find the individual  from_string from expr and replace indvidual character with to_string.

Parameter 

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

 

Return Data Type 

The string returned is in the same character set as expr.

Example 

  SELECT TRANSLATE('yogesh,Aumar,mishra',',Aa','_Pc') "translateex" from dual;
translateex      
-------------------
yogesh_Pumcr_mishrc