(vi) PL/SQL string

Source: Internet
Author: User

The PL/SQL string is actually an optional sequence of dimension-specification characters. A character can be a combination of numbers, letters, blanks, special characters, or all. PL/SQL provides three types of strings:
  fixed-length string : In such a string, the programmer specifies the length while declaring the string. The string is the right padding space to reach the specified length.
variable length string : In such a string, the maximum length can be up to 32,767, specified for the string, and does not need to be populated.
  character Large object (CLOB) : This is a variable-length string that can reach 128 megabytes.

The PL/SQL string can be a variable or literal value. String literals are enclosed inside the quotation marks. For example: ' This is a string literal. yiibai.com ' Or ' Hello World '
Text that includes single quotes in the string, requires two single quotes to be entered adjacent to each other, such as: ' This isn ' t what it looks like '

Declaration of a String variable
The Oracle database provides a number of string data types, such as: Char,nchar,varchar2,nvarchar2,clob and NCLOB. The data type preceded by an ' N ' is the "national character set" data type, which stores Unicode character data.
If you need to declare a variable-length string, you must provide the maximum length of the string. For example, the VARCHAR2 data type. The following example illustrates the declaration and use of some string variables:

DECLARE name VARCHAR2 ( -); Company VARCHAR2 ( -);   Introduction Clob; ChoiceChar(1); BEGIN Name:='John Smith'; Company:='Infotech'; Introduction:='hello! I"'m John Smith from Infotech.'; Choice:='y'; IF Choice='y'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 Smithinfotech Hello! I'm John Smith from Infotech.PL/sql procedure successfully completed


Two, PL/SQL string functions and operators
PL/SQL provides the join operator (| | ) is used to connect two strings. The following table provides the string functionality (functions) provided with PL/sql:

functions and uses
1 ASCII (x); Returns the ASCII value of the character X
2 CHR (x); returns the ASCII value of the character X
3 CONCAT (x, y); Concatenate strings x and Y, and return additional strings
4 initcap (x); Each word is converted to uppercase in the first letter x, and the string is returned
5 INSTR (x, find_string [, start] [, occurrence]); Search find_string in X and return to where it appears
6 INSTRB (x); Returns the position of a string in 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 string of bytes for a single-byte character set
9 LOWER (x); converts an x to a lowercase letter and returns the string
10 lpad (x, Width [, pad_string]); X fills the left with a space, and the total length of the string reaches the wide character
11 LTRIM (x [, trim_string]); Left trim character from X
12 NANVL (x, value); If x matches the special value of Nan (not a number) it returns its value, otherwise it returns X
13 nls_initcap (x); The same initcap function, but it can be specified using a different sort method nlssort
14 nls_lower (x); Similarly, the difference is that it can be specified using a different sorting method nlssort lower function
15 nls_upper (x); The same, except that it can use different sorting methods to specify the Nlssort upper function
16 nlssort (x); The method of changing the sorted character. This parameter must be specified before any NLS function; otherwise, the default sort is used
17 NVL (x, value); Returns NULL if x is null; Otherwise, X is returned
18 NVL2 (x, value1, value2); If x is not NULL, return value1; If x is null, the value2 is returned
19 REPLACE (x, search_string, replace_string); Search x for search_string and replace with replace_string it
20 rpad (x, Width [, pad_string]); Fill x to the right
21st RTRIM (x [, trim_string]); Trim from X Right
22 SOUNDEX (x); Returns a string containing the phonetic representation of X
23 SUBSTR (x, start [, length]); Returns a child of x starting at the position specified by start. Optional length is substring
24 SUBSTRB (x); Systems with single-byte characters in bytes instead of characters except for the same substr
25 TRIM ([Trim_char from) x); Trim x characters from left and right
26 UPPER (x); X is converted to uppercase and returns the string

(vi) PL/SQL string

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.