String Functions with example in SQL ,PLSQL

           -:        String functions in plsql               :--

PL/SQL offers the concatenation operator (||) for joining two strings. The following table provides the string functions provided by PL/SQL .

 

S.No Function & Purpose    
1 ASCII(x);Returns the ASCII value of the character x.    

2 CHR(x); Returns the character with the ASCII value of x.    

3 CONCAT(x, y);Concatenates the strings x and y and returns the appended string.    

4 INITCAP(x);Converts the initial letter of each word in x to uppercase and returns that string.    

5 INSTR(x, find_string [, start] [, occurrence]);Searches for find_string in x and returns the position          at which it occurs.    

7 LENGTH(x);Returns the number of characters in x.    

8 LENGTHB(x);Returns the length of a character string in bytes for single byte character set.    

9 LOWER(x);Converts the letters in x to lowercase and returns that string.    

10 LPAD(x, width [, pad_string]) ;Pads x with spaces to the left, to bring the total length of the string          up to width characters.    

11 LTRIM(x [, trim_string]);Trims characters from the left of x.    

17 NVL(x, value);Returns value if x is null; otherwise, x is returned.    

19 REPLACE(x, search_string, replace_string);Searches x for search_string and replaces it with                    replace_string.    

20 RPAD(x, width [, pad_string]);Pads x to the right.    

21 RTRIM(x [, trim_string]);Trims x from the right.    

22 SOUNDEX(x) ;Returns a string containing the phonetic representation of x.    

25 TRIM([trim_char FROM) x);Trims characters from the left and right of x.    

26 UPPER(x);Converts the letters in x to uppercase and returns that string.  

e.g. 1 

set serveroutput on;

  DECLARE 

   v_greet varchar2(100) := 'Good Morning !!!'; 

BEGIN 

   dbms_output.put_line(UPPER(v_greet)); 

    

   dbms_output.put_line(LOWER(v_greet)); 

    

   dbms_output.put_line(INITCAP(v_greet)); 

    

   /* retrieve the first character in the string */ 

   dbms_output.put_line ( SUBSTR (v_greet, 1, 1)); 

    

   /* retrieve the last character in the string */ 

   dbms_output.put_line ( SUBSTR (v_greet, -1, 1)); 

    

   /* retrieve five characters,  

      starting from the seventh position. */ 

   dbms_output.put_line ( SUBSTR (v_greet, 7, 5)); 

    

   /* retrieve the remainder of the string, 

      starting from the second position. */ 

   dbms_output.put_line ( SUBSTR (v_greet, 2)); 

     

   /* find the location of the first "e" */ 

   dbms_output.put_line ( INSTR (v_greet, 'e')); 

END; 

/

E.g 2 :

DECLARE 

   VGREET varchar2(30) := '......Hello World.....'; 

BEGIN 

   dbms_output.put_line(RTRIM(VGREET,'.')); 

dbms_output.put_line(LTRIM(VGREET, '.')); 

dbms_output.put_line(TRIM( '.'from VGREET)); 

END;