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 expensive items are the variable length strings that range between 0 to 128tb.
In Fixed-length strings, while declaring a string the developer will specify the length.
In Variable-length strings, the maximum length can be 32,767.
- Character large objects(CLOBs)
- Fixed-length strings
- Variable-length strings
Examples
The below example illustrates how string variable works .
[c]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
IToolsInfo
Hello! I'm David warner from IToolsInfo.
PL/SQL procedure successfully completed[/c]
In the above example, the string character values will be displayed as Hello!I'm David warner from IToolsInfo.
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. |
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.