STRING FUNCTIONS

· Upper

· Lower

· Initcap

· Length

· Rpad

· Lpad

· Ltrim

· Rtrim

· trim

· Translate

· Replace

· Soundex

· Concat (‘|| ‘Concatenation operator)

· ASCII

· Chr

· Substr

· Instr

· Decode

· Greatest

· Least

· Coalesce

Now we can discuss each one

Upper:-

This will convert the given string into uppercase.

Syntax: upper (string)

     Ex:

SQL> select upper ('computer') from dual;

Output:-

                                     OR

SQL>select upper (ename) from EMP;

LOWER

This will convert the given string into lowercase.

Syntax: lower (string)

Ex:

SQL> select lower('COMPUTER') from dual;

Output:-

                                               Or

SQL>select lower (ename) from EMP;

INITCAP:-

This will capitalize the initial letter of the string.

Syntax: initcap (string)

Ex:

SQL> select Initcap ('computer') from dual;

Output:-

                                   Or

SQL>select Initcap (ename) from EMP;

LENGTH

This will give length of the string.

Syntax:

length (string)

Ex:

SQL> select length ('computer') from dual;

Output:-

                                  Or 

SQL>select empno, ename, length (ename) from EMP;

Output:-

RPAD

This will allows you to pad the right side of a column with any set of characters.

Syntax: Rpad (string, length, [ padding char])

Ex:

SQL> select Rpad ('computer', 15,'*'), Rpad ('computer',15,'*#') from dual;

Output:-

-- Default padding character was blank space

LPAD

This will allows you to pad the left side of a column with any set of characters.

Syntax: Lpad (string, length, [padding char])

Ex:

SQL> select Lpad ('computer', 15,'*'), Lpad ('computer', 15,'*#') from dual;

Output:-

-- Default padding character was blank space.

LTRIM

This will trim off unwanted characters from the left end of string.

Syntax: Ltrim (string, [unwanted chars])

Ex:

SQL> select Ltrim ('computer’,’ co'), Ltrim ('computer’,’ COM') from dual;

Output:-

SQL> select Ltrim ('computer','puter'), Ltrim ('computer','omputer') from dual;

Output:-

--If you haven’t specified any unwanted characters it will display entire string.

RTRIM

This will trim off unwanted characters from the right end of string.

Syntax: Rtrim (string, [unwanted chars])

Ex:

SQL> select Rtrim ('computer','er'), Rtrim ('computer’,’er') from dual;

Output:-

SQL> select Rtrim ('computer’,’omputer’), Rtrim ('computer’,’ compute') from dual;

Output :-

--If you haven’t specify any unwanted characters it will display entire string

TRIM

This will trim off unwanted characters from the both sides of string.

Syntax: trim (unwanted chars from string)

Example:-

SQL> select trim (‘i’ from 'indiani') from dual;

Output:-

SQL> select trim (leading’i’ from 'indiani') from dual;       -- this will work as LTRIM

Output:-

SQL> select trim (trailing’i’ from 'indiani') from dual;       -- this will work as RTRIM

Output:-

TRANSLATE

This will replace the set of characters, character by character.

Syntax: translate (string, old chars, new chars)

Ex:

SQL> select translate ('india','in','xy') from dual;

Output:-

REPLACE

This will replace the set of characters, string by string.

Syntax: replace (string, old chars, [new chars])

Ex:

SQL> select replace ('india','in','xy'), replace (‘India’,’ in’) from dual;

Output:-

SOUNDEX

This function returns a phonetic representation (the way it sounds) of a string.

Syntax: Soundex (string)

The Soundex algorithm is as follows:

· The Soundex return value will always begin with the first letter of string1.

· The soundex function uses only the first 5 consonants to determine the NUMERIC portion of the return value, except if the first letter of string1 is a vowel.

· The soundex function is not case-sensitive. What this means is that both uppercase and lowercase characters will generate the same soundex return value.

Ex:

SQL> select * from emp where soundex (ename) = soundex ('SMIT');

Output:-

CONCAT

This will be used to combine two strings.

Syntax: Concat (string1, string2)

Ex:

SQL> select Concat ('computer',' operator') from dual;

Output:-

    If you want to combine more than two strings you have to use concatenation operator (||).

SQL> select 'how' || ' are' || ' you' from dual;

Output:-

ASCII

This will return the decimal representation in the database character set of the first

character of the string.

Syntax: ASCII (string)

Ex:

SQL> select ASCII ('a'), ASCII ('apple') from dual;

Output:-

CHR

This will return the character having the binary equivalent to the string in either the

database character set or the national character set.

Syntax: Chr (number)

Ex:

SQL> select Chr (97) from dual;

Output:-

SUBSTRING:-

Used to extract the particular portion of the string

Syntax:-

SQL>Select Substr (<’string’>, M, N) from dual or ;
  
String-represents the name of the string
M- starting position
N- no. of characters to be extracted (optional)
Example:-
SQL>select Substr (‘oracle11g’, 1, 5) from dual;
Output:-
 
                  
 
Display the employee’s names whose name start with same letter and end with same letter
SQL>select *from emp where Substr (ename, 1, 1) =Substr (ename, length (ename), 1);
<<<<<<>>>>>>>>
 
INSTRING:-
Returns number.
Gives the position of the character ‘C’ in the given string ‘S’  
 Syntax:-
SQL>select Instr(S, C, M, N) from dual or 
;
 
S – Given string
C – Character
M—Staring position
N—occurrence number
 
Example:-
 
SQL>select Instr (‘ANAND’,’A’,’1’) from dual;
Output:-
               
 
 
SQL>select Instr (‘ANAND’,’A’,’2’) from dual;
Output:-
                     
 
 
 
DECODE:-
  
Used to check for multiple      conditions while manipulating or retrieving the data
  
It implements “IF” construct logic
  
It will check for equality      condition only
  
If the number of parameters are      odd and different then decode will display nothing.
  
If the number of parameters are      even and different then decode will display last value.
  
If all the parameters are null      then decode will display nothing.
  
If all the parameters are zeros      then decode will display zero.
 
 
Syntax: decode (value, if1, then1, if2, then2… else);
 
Example:-
SQL>select sal, decode (sal, 500,'Low', 5000,'High','Medium') from emp;
Output:-
                       
 
SQL>Select ename, sal, deptno, decode (deptno, 10, sal*0.15,
                                                                          20, sal*.25,
                                                           30, sal*.35,
                                                                Sal*.45) bonus from emp;
 
Output:-
                     
 
 
 
GREATEST
     This will give the greatest string.
     Syntax: greatest (strng1, string2, string3 … stringn)      
     Ex:
SQL> select greatest ('a', 'b', 'c'), greatest ('satish','srinu','saketh') from dual;
Output:-
                    
 
Ø  If all the parameters are nulls then it will display nothing.
Ø  If any of the parameters is null it will display nothing.
 
LEAST
    This will give the least string.
    Syntax: least (strng1, string2, string3 … stringn) 
    Ex:
SQL> select least ('a', 'b', 'c'), least ('satish','srinu','saketh') from dual;
Output:-
               
 
·        If all the parameters are nulls then it will display nothing.
·        If any of the parameters is null it will display nothing.
COALESCE
    This will give the first not-null string.
    Syntax: coalesce (strng1, string2, string3 … stringn)       
    Ex:
SQL> select coalesce ('a','b','c'), coalesce (null,'a',null,'b') from dual;
Output:-