LAB $4$ Content

PL$/$SQL $-$ Strings

The string in PL$/$SQL is actually a sequence of characters with an optional size specification. The characters could be numeric, letters, blank, special characters or a combination of all. PL$/$SQL offers three kinds of strings $$

$$ Fixed$-$length strings$$ In such strings, programmers specify the length while declaring the string. The string is right$-$padded with spaces to the length so specified.

$$ Variable$-$length strings$$ In such strings, a maximum length up to $32,767,$ for the string is specified and no padding takes place.

$$ Character large objects $($CLOBs$)$ These are variable$-$length strings that can be up to $128$ terabytes.

PL$/$SQL strings could be either variables or literals. A string literal is enclosed within quotation marks. For example,

'This is a string literal.$\text{'}$ Or 'hello world'

To include a single quote inside a string literal, you need to type two single quotes next to one another. For example,

'this isn'$\text{'}$t what it looks like'

Declaring String Variables

Oracle database provides numerous string datatypes, such as CHAR, NCHAR, VARCHAR$2,$ NVARCHAR$2,$ CLOB, and NCLOB. The datatypes prefixed with an$\text{'}$N$\text{'}$are$\text{'}$national character set'$$datatypes$,$ that store Unicode character data.

If you need to declare a variable$-$length string, you must provide the maximum length of that string. For example, the VARCHAR$2$ data type. The following example illustrates declaring and using some string variables $$

DECLARE

name varchar$2(20)$;

company varchar$2(30)$;

introduction clob;

choice char$(1)$;

BEGIN

name :$=$ 'John Smith';

company :$=$ 'Infotech';

introduction :$=\text{'}$ Hello! I$\text{'}\text{'}$m John Smith from Infotech.$\text{'}$;

choice :$=\text{'}$y$\text{'}$;

IF choice $=\text{'}$y$\text{'}$ THEN

dbms$\_$output.put$\_$line$($name$)$;

dbms$\_$output.put$\_$line$($company$)$;

dbms$\_$output.put$\_$line$($introduction$)$;

END IF;

END;

$/$

When the above code is executed at the SQL prompt, it produces the following result $$

John Smith

Infotech

Hello! I$\text{'}$m John Smith from Infotech.

PL$/$SQL procedure successfully completed

To declare a fixed$-$length string, use the CHAR datatype. Here you do not have to specify a maximum length for a fixed$-$length variable. If you leave off the length constraint, Oracle Database automatically uses a maximum length required. The following two declarations are identical $$

red$\_$flag CHAR$(1)$ :$=\text{'}$Y$\text{'}$;

red$\_$flag CHAR :$=\text{'}$Y$\text{'}$;

PL$/$SQL String Functions and Operators

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.

$6$ INSTRB$($x$)$;

Returns the location of a string within another string, but returns the value in bytes.

$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$.$

$12$ NANVL$($x$,$ value$)$;

Returns value if x matches the NaN special value $($not a number$),$ otherwise$$x$$is returned.

$13$ NLS$\_$INITCAP$($x$)$;

Same as the INITCAP function except that it can use a different sort method as specified by NLSSORT.

$14$ NLS$\_$LOWER$($x$)$ ;

Same as the LOWER function except that it can use a different sort method as specified by NLSSORT.

$15$ NLS$\_$UPPER$($x$)$;

Same as the UPPER function except that it can use a different sort method as specified by NLSSORT.

$16$ NLSSORT$($x$)$;

Changes the method of sorting the characters. Must be specified before any NLS function; otherwise, the default sort will be used.

$17$ NVL$($x$,$ value$)$;

Returns value if$$x$$is null; otherwise, x is returned.

$18$ NVL$2($x$,$ value$1,$ value$2)$;

Returns value$1$ if x is not null; if x is null, value$2$ 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$.$

$23$ SUBSTR$($x$,$ start $[,$ length$])$;

Returns a substring of$$x$$that begins at the position specified by start. An optional length for the substring may be supplied.

$24$ SUBSTRB$($x$)$;

Same as SUBSTR except that the parameters are expressed in bytes instead of characters for the single$-$byte character systems.

$25$ TRIM$([$trim$\_$char FRO