String Functions in Oracle SQL

     3shTech (trainings@trishtechnology.com)

Numeric Functions

Group Functions

Date Functions

Conversion Functions

Analytical Functions

String Functions

Below are some of the string functions used to manipulate character information in strings.

·       UPPER

·       LOWER

·       INITCAP

·       LENGTH

·       RPAD

·       LPAD

·       LTRIM

·       RTRIM

·       TRIM

·       TRANSLATE

·       REPLACE

·       ASCII

·       CHR

·       SUBSTR

·       INSTR

·       DECODE

·       CASE

·       GREATEST

·       LEAST

·       COALESCE

1)Upper:

This function will convert the given string into uppercase.

Syntax: UPPER (string)

SQL> SELECT UPPER (‘computer’) FROM DUAL;
 
Output:-

 
SQL>SELECT UPPER (ename) FROM emp;

 
2)LOWER:

This function will convert the given string into lowercase.

Syntax: LOWER (string)

SQL> SELECT LOWER (‘COMPUTER’) FROM DUAL;
 
Output:-

 
SQL>SELECT LOWER (ename) FROM emp;

Output:-

 

3)INITCAP:

This function will capitalize the initial letter of the string.

Syntax: INITCAP (string)

SQL> SELECT INITCAP (‘computer’) FROM DUAL;
 
Output:-

SQL>SELECT INITCAP (ename) FROM emp;

Output:-

 
4)LENGTH:

This function will give length of the string.

Syntax:

LENGTH (string)

SQL> SELECT LENGTH (‘computer’) FROM DUAL;
 
Output:-

SQL>SELECT empno, ename, LENGTH (ename) FROM emp;
 
Output:-

 

5)RPAD:

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

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

SQL> SELECT RPAD (‘computer’, 15,’*’), RPAD (‘computer’,15,’*#’) FROM DUAL;
 
Output:–

 

— Default padding character was blank space

6)LPAD:

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

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

SQL> SELECT LPAD (‘computer’, 15,’*’), LPAD (‘computer’, 15,’*#’) FROM DUAL;
 
Output:–

 

— Default padding character was blank space.
 
7)LTRIM:

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

Syntax: LTRIM (string, [unwanted chars])

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.
 
8)RTRIM:

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

Syntax: RTRIM (string, [unwanted chars])

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.
 
9)TRIM:

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

Syntax: TRIM (unwanted chars FROM string)

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:-

 

10)TRANSLATE:

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

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

SQL> SELECT TRANSLATE (‘india’,’in’,’xy’) FROM DUAL;
 
Output:-

 
11)REPLACE:

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

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

SQL> SELECT REPLACE (‘india’,’in’,’xy’), REPLACE (‘India’,’ in’) FROM DUAL;
 
Output:–

  

 
12)ASCII:

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

character of the string.

Syntax: ASCII (string)

SQL> SELECT ASCII (‘a’), ASCII (‘apple’) FROM DUAL;
 
Output:–

 
13)CHR:

This function will return the character having the binary equivalent of the input in either the

database character set or the national character set.

Syntax: CHR (number)

SQL> SELECT CHR (97) FROM DUAL;
 
Output:-

 
14)SUBSTRING:

This function is used to extract the particular portion of the string

Syntax:-

SQL>SELECT SUBSTR (, < starting position >, < no. of characters to be extracted >) FROM;

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);

–no rows selected

 
15)INSTRING:

This function is used to extract the position of the character in given string.

Syntax:-

SQL>SELECT INSTR(<input type="text" />, < Character to search>, < Staring position >, < occurrence number >) FROM;

Example:-

SQL> SELECT INSTR (‘ANAND’,’A’,1,2) FROM DUAL;
 
Output:-

INSTR (‘ANAND’,’A’,1,2)

———————— 3

 
16)DECODE:

·       Decode is used to check for multiple conditions while manipulating or retrieving the data.

·       It implements “IF” construct logic

·       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’) range 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:–

 
17)GREATEST:

This will give the greatest string.

Syntax: GREATEST (strng1, string2, string3 … stringn)

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.

 
18)LEAST:

This function will give the least string.

Syntax: LEAST (strng1, string2, string3 … stringn)

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.

 
19)COALESCE:

This function will give the first not-null string.

Syntax: COALESCE (strng1, string2, string3 … stringn)

SQL> SELECT COALESCE (‘a’,’b’,’c’), COALESCE (null,’a’,null,’b’) FROM DUAL;
 
Output:-

 

 

20)CASE:

CASE Example:

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

a)

SELECT empno,deptno,sal OLDSAL, CASE deptno WHEN 40 THEN sal + 1000

                                            WHEN 10 THEN sal + 500

                                            WHEN 20 THEN sal + 200

                                            ELSE sal

                                            END  NEWSAL

  FROM emp;

 

b) 

 

SELECT empno,deptno,sal OLDSAL, CASE   WHEN  (deptno BETWEEN 5 AND 15) THEN sal + 500

                                      WHEN  (deptno BETWEEN 16 AND 25) THEN sal + 600

                                      WHEN  (deptno BETWEEN 35 AND 45) THEN sal + 1500

                                      ELSE sal + 100

                                      END  NEWSAL    

  FROM emp

 ORDER BY 2 DESC;

 

22)

deptno    GRADE

    10        A

    20        B

    30        C

    others    D

 

 

Empno   ename    sal     Grade 

 

SELECT empno, ename , sal,deptno, DECODE(deptno ,10 , 'A'

                                                ,20 , 'B'

                                                ,30 , 'C'

                                                 ,'D')  "Grade of employees"

  FROM emp;

 

Hope you have gained some command over STRING functions.

Thank you!!  

😊