To the point guide to PL/SQL

Strings are a group of characters the characters could be letters, numeric, special characters, blank.

Types of strings:

• Variable-length strings: Here a string has a maximum length up to 32,767, for the specified string and no padding takes place.
• Fixed-length strings: Here the length of string is defined while declaring the string by developers.
• Character large objects (CLOBs): Here a string that can be up to 128 terabytes.

 

Declaring String Variables

We have numerous data types of string such as _{CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2} and NCLOB where ‘N’ prefixes to store Unicode character data is known as 'national character set'

Example

DECLARE
   Stud_nam varchar2(25);
   college varchar2(20);
   About_College clob;
   BEGIN
   name := 'Nimrit';
   college := 'IIN';
   About_College:= ' IIN is a College of Engineering.';
       dbms_output.put_line(Stud_nam);
      dbms_output.put_line(college);
      dbms_output.put_line(About_College);
   END IF;
END;
/

Result

Nimrit
IIN
IIN is a College of Engineering.
PL/SQL procedure successfully completed

Functions of String

ASCII( single character )

Returns the ASCII value of the single character.

Example:

ASCII('A')

ASCII('a')

Output: 65

Output: 97

ASCIISTR( string ):

This function converts a string to an ASCII string.

Example:

ASCIISTR('A B C Ä Ê Í Õ')

Result:

'A B C \00C4 \00CA \00CD \00D5'

CHR(number):

Returns the character with the ASCII value of given number.

Example:

CHR(97)

Result:

'a'

Example 2

CHR(65)

Result:

'A'

CONCAT(str1, str2):

Returns the concatenation of given string str1 and str2.

Example:

CONCAT('ab', 'cd')

Result:

'abcd'

DECOMPOSE(str):

Returns a Unicode for given string.

Example:

DECOMPOSE('Tolé')

Result:

'Tole´'

INITCAP(str1):

sets the first letter in each word to uppercase and the rest to lowercase.

Example:

INITCAP('plsql database');

Result:

'Plsql Database'

INSTR(str1, substring [, start_position [, nth_appearance ] ]):

Returns the location of a substring in a string.

Example:

INSTR('Tree', 'e')

Result:

3 (the first occurrence of 'e')

Example 2:

INSTR('Tree', 'e', 1, 2)

Result:

4 (the second occurrence of 'e')

INSTRB(str1, substring [, start_position [, nth_appearance ] ]):

Returns the location of a substring in a string, using bytes.

Example:

INSTRB('Tree', 'e')

Result:

3 (the first occurrence of 'e' - single-byte character set)

LENGTH(str):

Returns the number of characters in given string.

Example:

LENGTH('Tree')

Result:

4

LENGTHB(str):

Returns the length of a character string in bytes for single byte character set.

Example:

LENGTHB('ab')

Result:

2 (single-byte character set)

LOWER(str):

Converts the given string characters to lowercase.

Example:

LOWER('TREE');

Result:

'tree'

LPAD(str1, padded_length, [ pad_string]):

Pads str1 with spaces to left, to bring the total length of the string up to width characters.

Example:

LPAD('tree', 2);

Result:

'tr'

LTRIM( str1, [ trim_string ] ):

Trims characters from the left of string.

Example:

LTRIM('dddabc', 'd')

Result:

'123'

NCHR(n):

Returns the character based on its number code in the national character set.

Example:

NCHR(97)

Result:

'a'

REPLACE:

replaces a sequence of characters in a string with another set of characters.

Example:

REPLACE('11abcd', '1', '0');

Result:

'00abcd'

RPAD( str1, padded_length, [ pad_string ] ):

pads the right-side of a string with a specific set of characters.

Example:

RPAD('tree', 2)

Result:

'tr'

RTRIM( string1, [ trim_string ] ):

Removes specified characters from the right-side of a string.

Example:

RTRIM('tree ', ' ')

Result:

'tree'

SOUNDEX( str1 ):

Returns a string containing the phonetic representation of str1.

Example:

SOUNDEX('apples')

Result:

'A142'

SUBSTR( str, start_position, [ length ] ):

user can extract a substring from a string.

Example:

SUBSTR('Tree is green', 6, 2)

Result:

'is'

TO_CHAR( value, [ format_mask ], [ nls_language ] ):

converts a number or date to a string.

Example:

TO_CHAR(1220.83, '90.234')

Result:

'1,220.83'

TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] str1 ):

Removes all specified characters either from the beginning or the ending of a string.

Example:

TRIM(' ' FROM ' tree ')

Result:

'tree'

UPPER( str1 ):

converts the specified string to uppercase.

Example:

UPPER('tree 123')

Result:

'TREE 123'