PL/SQL - SPLessons

PL/SQL String

Chapter 16

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PL/SQL String

PL/SQL String

shape Description

The approach of PLSQL String is indeed an arrangement of characters with a discretionary size designation. The character could be a letter, blank, numeric and other special character.

PLSQL String is of three types. They are:

Character Large Objects(CLOBs)

Character expensive items are the variable length strings that range between 0 to 128tb.

Fixed-length Strings

In Fixed-length strings, while declaring a string the developer will specify the length.

Variable-length String

In Variable-length strings, the maximum length can be 32,767.

shape Examples

The below example illustrates how string variable works .

SQL> declare
  2  name varchar2(20);
  3  company varchar2(30);
  4  introduction clob;
  5  choice char(1);
  6  begin
  7  name:='DAVID WARNER';
  8  company:='IToolsInfo';
  9  introduction:='Hello!I'm David warner from IToolsInfo.';
 10  choice:='y';
 11  IF choice='y'THEN
 12  dbms_output.put_line(name);
 13  dbms_output.put_line(company);
 14  dbms_output.put_line(introduction);
 15  END IF;
 16  END;
 17  /
David warner
Hello! I'm David warner from IToolsInfo.
PL/SQL procedure successfully completed

In the above example, the string character values will be displayed as Hello!I’m David warner from IToolsInfo.

PL/SQL String Functions and Operators

shape Description

The concatenation operator || in PL/SQL is used for joining the two strings. The following table contains all the string functions supported by PL/SQL:

S.NO Function Purpose
1 CHR(X); Returns the character with the ASCII code of x.
2 ASCII(X); Returns the ASCII code of the character x.
3 INITCAP(X) Capitalizes the first letter of every word in x and returns that string.
4 CONCAT(X,Y); Links the strings x & y and returns the annexed string.
5 INSTR(X,find_string [,start] [,occurrence]); Checks for find_string in x and returns the position.
6 LENGTH(X) Returns the number of characters in x.
7 INSTRB Returns the area of a string inside the other string and returns the quality in bytes.
8 LENGTHHB(X); Returns the length of a character string in bytes for a single byte character set.
9 LPAD(X,Width [,pad_string]); Cushions x with spaces to the left to bring the aggregate length of the string up to width characters.
10 LOWER(X) Changes the letters in X to lower case and returns that string.
11 LTRIM(X,trim_string]); Trims characters from the left of x.
12 NLS_INITCAP(x); Same as the INITCAP function, except that it can use an alternate sort technique as determined by NLSSORT.
13 NANVL(x,value); Returns value if x coordinates the NaN uncommon worth, generally x is returned.
14 NLS_LOWER(X) Same as the LOWER capacity aside from that it can make use of an alternate sort strategy as indicated by NLSSORT.
15 NLS_UPPER(X); Same as the UPPER capacity aside from that it can make use of an alternate sort strategy as indicated by NLSSORT.
16 NVL(x,value); Returns the value if x is null; otherwise, x is returned.
17 NLSSORT(x); Changes the strategy for sorting the characters. Must be determined before any NLS capacity; generally, the default sort will be used.
18 NVL2(x,value1,value2); Returns value1 if x is not null; if x is null, value2 is returned.
19 REPLACE(x,search_string,replace_string); Checks x in the search_string and replaces it with the replace_string.
20 RPAD(X,Width [,pad_string]); Pads x to the right.
21 RTRIM(x[,trim_string]); Trims x from the right.
22 UPPER(x); Converts the letter in x to uppercase and returns that string.
23 TRIM([trim_char FROM)x); Trims character from the left and right of x.
24 SOUNDEX(x); Returns a string containing the phonetic representation of x
25 SUBSTR(x,start [,length]); Returns a substring of x that begins at the position specified by start. An optional length for the single-byte character systems.


shape Key Points

  • PLSQL String – Is a sequence of characters.
  • Character large objects – Are the variable length strings.
  • Fixed length strings – Are right cushioned with space to the length.
  • Variable length strings – In which the maximum length will be indicated.