Strings
Fixed-length stringsVariable-length strings
Character large objects (CLOBs)
Declaring String Variables
DECLARE
v_name varchar2(20);
v_company varchar2(30);
v_introduction clob;
v_choice char(1);
BEGIN
v_name := 'Mr. Hareesh';
v_company := 'H Group';
v_introduction := ' Hello! I''m Hareesh Pothuguntla from H Group.';
v_choice := 'y';
IF choice = 'y' THEN
dbms_output.put_line(v_name);
dbms_output.put_line(v_company);
dbms_output.put_line(v_introduction);
END IF;
END;
/
v_name varchar2(20);
v_company varchar2(30);
v_introduction clob;
v_choice char(1);
BEGIN
v_name := 'Mr. Hareesh';
v_company := 'H Group';
v_introduction := ' Hello! I''m Hareesh Pothuguntla from H Group.';
v_choice := 'y';
IF choice = 'y' THEN
dbms_output.put_line(v_name);
dbms_output.put_line(v_company);
dbms_output.put_line(v_introduction);
END IF;
END;
/
If you want to declare a fixed-length string, use the CHAR datatype.
v_flag CHAR := 'Y';
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);
Example 1
DECLARE
v_greetings varchar2(11) := 'hello world';
BEGIN
dbms_output.put_line(UPPER(v_greetings));
dbms_output.put_line(LOWER(v_greetings));
dbms_output.put_line(INITCAP(v_greetings));
/* retrieve the first character in the string */
dbms_output.put_line ( SUBSTR (v_greetings, 1, 1));
/* retrieve the last character in the string */
dbms_output.put_line ( SUBSTR (v_greetings, -1, 1));
/* retrieve five characters,
starting from the seventh position. */
dbms_output.put_line ( SUBSTR (v_greetings, 7, 5));
/* retrieve the remainder of the string,
starting from the second position. */
dbms_output.put_line ( SUBSTR (v_greetings, 2));
/* find the location of the first "e" */
dbms_output.put_line ( INSTR (v_greetings, 'e'));
END;
/
Example 2
v_greetings varchar2(11) := 'hello world';
BEGIN
dbms_output.put_line(UPPER(v_greetings));
dbms_output.put_line(LOWER(v_greetings));
dbms_output.put_line(INITCAP(v_greetings));
/* retrieve the first character in the string */
dbms_output.put_line ( SUBSTR (v_greetings, 1, 1));
/* retrieve the last character in the string */
dbms_output.put_line ( SUBSTR (v_greetings, -1, 1));
/* retrieve five characters,
starting from the seventh position. */
dbms_output.put_line ( SUBSTR (v_greetings, 7, 5));
/* retrieve the remainder of the string,
starting from the second position. */
dbms_output.put_line ( SUBSTR (v_greetings, 2));
/* find the location of the first "e" */
dbms_output.put_line ( INSTR (v_greetings, 'e'));
END;
/
Example 2
DECLARE
v_greetings varchar2(30) := '......Hello World.....';
BEGIN
dbms_output.put_line(RTRIM(v_greetings,'.'));
dbms_output.put_line(LTRIM(v_greetings, '.'));
dbms_output.put_line(TRIM( '.' from v_greetings));
END;
/
v_greetings varchar2(30) := '......Hello World.....';
BEGIN
dbms_output.put_line(RTRIM(v_greetings,'.'));
dbms_output.put_line(LTRIM(v_greetings, '.'));
dbms_output.put_line(TRIM( '.' from v_greetings));
END;
/
No comments:
Post a Comment