Character Functions

Lower Function:
Ø  It converts alpha character values to lower case.
Ø  The return value has the same data type as argument char type (Char or Varchar2)
Syntax: lower (column/expression)
Ex:
  • Select ’ORACLE CORPORATION’ string, LOWER (’ORACLE CORPORATION’) lower from dual;
  • Select ename, lower(ename) lower from emp;
  • Select ename, job, sal from emp where lower(job) = ’manager’;
Upper Function:
Ø  It converts the alpha character values to upper case.
Ø  The return value has the same data type as the argument char.
Syntax: Upper (column/expression)
Ex:
  • Select ’oracle corporation’ string, upper (’oracle corporation’) upper from dual;
  • Select ename, lower(ename), upper(ename) from emp;
  • Select ename,job, sal from emp where job = upper (’manager’);
  • Select ename, job , sal from emp where job = upper(lower (’MANAGER’));
  • Select upper (’the’ ||ename||’ basic salary is Rupees ’||Sal) from emp where job IN (’MANAGER’,upper (’clerk’)) order by sal DESC;
INITCAP Function:
Ø  It converts the alpha character values into uppercase for the first letter of each word, keeping all other letters in lower case.
Ø  Words are delimited by white space or characters that are not alphanumeric.
Syntax: initcap (column/expression)
Ex:
  • Select ’oracle corporation’ string, initcap (’oracle corporation’) initcap from dual;
  • Select ename, upper(ename), lower(ename),initcap(ename) from emp;
CONCAT Function:
Ø  It concatenates the first characters value to the second character value.
Ø  It accepts only two parameters.
Ø  It returns the character data type.
Syntax: Concat (column1/expr1 , column2/expr2)
Ex:
  • Select ’Oracle’ string1, ’corporation’ string2, concat (’oracle’,’corporation’) concat from dual;
  • Select ename, job, concat(ename,job) concat from emp;
  • Select concat (’The Employee Name is ’, initcap(ename)) info from emp;
  • Select concat(concat(initcap(ename), ’ is a ’), job) job from emp;
Length Function:
Ø  Returns the number of characters in a value
Ø  If the string has data type char, the length includes all trailing blanks.
Ø  If the string is null, it returns null.
Syntax: length (column/expression)
Ex:
  • Select ’Oracle’ string, length (’oracle’) length from dual;
  • Select length(ename)||’ characters exit in ’||INITCAP(ename)||’ s name.’ as “names and lengths” from emp;
  • Select initcap(ename), job from emp where length(job) = 7;


Sub String Function:
Ø  Returns specified characters from character value, starting from a specified position ‘m’ to ‘n’ characters long.
Syntax: substr(col/expr, m,n)
Points to remember:
Ø  If “m” is 0, it is treated as 1.
Ø  If “m” is positive, oracle counts from the beginning of string to find the first character.
Ø  If “m” is negative, oracle counts backwards from the end of the string.
Ø  If “n” is omitted, oracle returns all characters to the end of string.
Ø  If “n” is less than 1 or 0, A NULL is returned.
Ø  Floating point numbers passed as arguments to substr are automatically converted to integers.
Ex:
  • Select ’ABCDEFGH’ string, substr (’ABCDEFGH’,3,4) substring from dual;
  • Select ’ABCDEFGH’ string, substr (’ABCDEFGH’,-5,4) substring from dual;
  • Select ’ABCDEFGH’ string, substr (’ABCDEFGH’,0,4) substring from dual;
  • Select ’ABCDEFGH’ string, substr (’ABCDEFGH’,4) substring from dual;
  • Select ’ABCDEFGH’ string, substr(’ABCDEFGH’,4,0) substring from dual;
  • Select ’ABCDEFGH’ string, substr(’ABCDEFGH’,4,-2) substring from dual;
  • Select ename, job from emp where substr(job,4,3) = upper(’age’);
  • Select concat(initcap(ename),concat(’ is a ’, concat(initcap(substr(job, 1, 3)), ’Eater’)))
From emp where substr(job,4,3) = upper(’Age’);
INSTRING Function:
Ø  It returns the numeric position of a named character.
Syntax: instr(column/expression , char, m, n)
Ø  The INSTR functions search string for substring that is supplied.
Ø  The function returns an integer indicating the position of the character in string that is the first character of this occurrence.
Ø  Searches for column or expression beginning with its ‘m’th character for the ‘n’th occurrence of char2, and returns the position of the character in char1, that is the first character of this occurrence.
Ø  ‘m’ can be positive or negative, if negative searches backward from the end of column or expression.
Ø  The value of ‘n’ should be positive.
Ø  The default values of both ‘m’ and ‘n’ are 1.
Ø  The Return value is relative to the beginning of char1 regardless of the value of ‘m’, and is expressed in characters.
Ø  If the search is unsuccessful, the return value is Zero.
Ex:
  • Select ’STRING’ string, instr(’STRING’,’R’) instring from dual;
  • Select ’CORPORATE FLOOR’ string, instr(’CORPORATE FLOOR’,’OR’,3,2) instring from dual;
  • Select ’CORPORATE FLOOR’ string, instr(’CORPORATE FLOOR’,’OR’,-3,2) instring from dual;
  • Select job, instr(job,’A’,1,2) position from emp where job = ’MANAGER’;
  • Select job, instr(job, ’A’,2,2) position from emp where job = ’MANAGER’;
  • Select job, instr(job, ’A’,3,2) position from emp where job = ’MANAGER’;
  • Select job, instr(job, ’A’,2) position from emp where job = ’MANAGER’;
Lpad Function:
Ø  Pads the character value right justified to a total width of ‘n’ character positions.
Ø  The default padding character is space.
Syntax: lpad(char1, n, ‘char2’)
Ex:
  • Select ’page 1’ string, lpad (’page 1’,15,’*’) lpadded From dual;
  • Select ’page 1’ string, lpad(’page 1’,15) lpadded From dual;
  • Select ename, lpad(ename,10,’-’) lpadded from emp where sal >= 2500;
Rpad Function:
Ø  Pads the character value left justified to a total width of ‘n’ character positions.
Ø  The default padding character is space.
Syntax: Rpad(char1, n,’char2’)
Ex:
  • Select ’page 1’ string, rpad (’page 1’,15,’*’) rpadded from dual;
  • Select ’page 1’ string, rpad (’page 1’,15) rpadded from dual;
  • Select ename,rpad(ename,10,’-’) rpadded from emp where sal >= 2500;
  • Select Ename, lpad(ename,10,’-’) lpadded, rpad(ename,10,’-’) rpadded from emp;
  • Select Ename, lpad(rpad(ename,10,’-’) centered from emp;
Ltrim Function:
Ø  It enables to trim heading characters from a character string.
Ø  All the leftmost characters that appear in the set are removed.
Syntax: ltrim(char, set)
Ex:
  • Select ’xyzXxyLAST WORD’ string, ltrim(’xyzXxyLAST WORD’, ’xy’) ltrimmed from dual;
  • Select job, ltrim(job,’MAN’) ltrimmed from emp where job like ’MANAGER’;
Rtrim Function:
Ø  It enables the training of trailing characters from a character string.
Ø  All the right most characters that appear in the set are removed.
Syntax: rtrim(char, set)
Ex:
  • Select ’BROWNINGyxXxy’ string, rtrim(’BROWINGyxXxy’,’xy’) rtrimmed from dual;
  • Select rtrim(job,’ER’), job from emp where ltrim(job, ’MAN’) like ’GER’;
Trim Function:
Ø  It enables to trim heading or trailing characters or both from a character string.
Ø  If leading is specified concentrates on leading characters.
Ø  If trailing is specified concentrates on trailing characters.
Ø  If both or none is specified concentrates both on leading and trailing.
Syntax: trim(leading/trailing/both, trim char from trim source)
Ex:
  • Select ’MITHSS’ string, trim(’S’ from ’MITHSS’) trimmed from dual;
  • Select ’SSMITH’ string, trim (’S’ from ’SSMITH’) trimmed from dual;
  • Select ’SSMITHSS’ string, trim(’S’ from ’SSMITHSS’) trimmed from dual;
  • Select ’SSMITHSS’ string, trim(leading ’S’ from ’SSMITHSS’) trimmed from dual;
  • Select ’SSMITHSS’ string, trim(trailing ’S’ from ’SSMITHSS’) trimmed from dual;
  • Select ’SSMITHSS’ string, trim(both ’S’ from ’SSMITHSS’) trimmed from dual;
Replace Function:
Ø  It returns the every occurrence of search string replaced by the replacement string.
Ø  If the replacement string is omitted or null, all occurrence of search string are removed.
Ø  It substitutes one string for another as well as removes characters strings.
Syntax: replace(char, search_str, replace_str)
Ex:
  • Select ’JACK AND JUE’ string, replace(’JACK AND JUE’,’J’,’BL’) Replaced from dual;
  • Select ’JACK AND JUE’ string, replace(’JACK AND JUE’,’j’,’BL’) Replaced from dual;
  • Select Ename, replace(job,’MAN’,’DAM’) replaced from emp where job=’MANAGER’;
  • Select job, replace(job, ’p’) from emp where job=’PRESIDENT’;
  • Select job, replace(job, ’MAN’, ’EXECUTIVE’) from emp where job=’SALESMAN’;
Translate Function:
Ø  It used to translate character by character in a string.
Syntax: Translate (CHAR, FROM, To)
Ø  It returns a CHAR with all occurrences of each character in ‘FROM’ replaced by its corresponding character in ‘TO’.
Ø  Characters in CHAR that are not in FROM are not replaced.
Ø  The argument FROM can contain more characters than TO.
Ø  If the extra characters appear in Char, they are removed from the return value.
Ex:
  • Select job, translate(job, ’p’,’ ’) from emp where job = ’PRESIDENT’;
  • Select job translate(job, ’MN’,’DM’) from emp where job = ’MANAGER’;
  • Select job, translate(job, ’A’,’O’) from emp where job=’SALESMAN’;
CHR Function:
Ø  It returns a character having the ASCII equivalent to ‘n’.
Ø  It returns the equivalent for ‘n’ in database character set or national character set.
Syntax: CHR(n)
Ex:
  • Select chr(67)||chr(65)||chr(84) sample from dual;
ASCII Function:
Ø  It returns the ASCII representation in the character database set of the first characters of the char.
Syntax: ASCII(char)

  • Select ASCII(’A’), ASCII(’APPLE’) from dual;