String Functions and Operators


The concatenation operator (||) is used for joining two strings.

String functions are:

  • ASCII(x);
  • CHR(x);
  • CONCAT(x, y);
  • INITCAP(x);
  • INSTR(x, find_string [, start] [, occurrence]);
  • INSTRB(x); 
  • LENGTH(x); 
  • LENGTHB(x); 
  • LOWER(x); 
  • LPAD(x, width [, pad_string]) ; 
  • LTRIM(x [, trim_string]); 
  • NANVL(x, value); 
  • NLS_INITCAP(x); 
  • NLS_LOWER(x) ; 
  • NLS_UPPER(x); 
  • NLSSORT(x); 
  • NVL(x, value); 
  • NVL2(x, value1, value2); 
  • REPLACE(x, search_string, replace_string); 
  • RPAD(x, width [, pad_string]); 
  • RTRIM(x [, trim_string]); 
  • SOUNDEX(x) ; 
  • SUBSTR(x, start [, length]); 
  • SUBSTRB(x); 
  • TRIM([trim_char FROM) x); 
  • UPPER(x);

 Sample code:

DECLARE

v_company_name varchar2(11) := 'Ask Haressh Technical Blog';

BEGIN

dbms_output.put_line(UPPER(v_company_name)); -- Convert string to upper case

dbms_output.put_line(LOWER(v_company_name)); -- Convert string to lower case

dbms_output.put_line(INITCAP(v_company_name)); -- Convert string as First letter as Upper and remaining as lower case

dbms_output.put_line ( SUBSTR (v_company_name, 1, 1)); -- Get the first letter from string

dbms_output.put_line ( SUBSTR (v_company_name, -1, 1)); -- Get the last letter from string


/* retrieve the five characters, 

starting from the seventh position. */

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


/* retrieve the remaining of the string,

starting from the second position. */

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


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

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

END;


Sample Code:

DECLARE

v_company_name varchar2(30) := '......Ask Hareesh.....';

BEGIN

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

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

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

END;

*/

No comments:

Post a Comment