SQL Record-plsql string

PL/SQL string

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 quotation marks in a string, you need to enter two single quotes adjacent to each other, such as:

' This isn ' t what it looks like '

Declaring 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:

DECLAREName Varchar2(20);Company VARCHAR2(30);Introduction CLOB;Choice Char(1);BEGINName:= ' John Smith ';Company:= ' Infotech ';Introduction:= ' Hello! I ' m John Smith from Infotech. ';Choice:= ' Y '; IF Choice =  ' y '  then Dbms_output.name Dbms_output put_line (company. Put_lineintroductionend if; end;                

When the above code is executed at the SQL prompt, it produces the following results:

John Smithinfotech corporationhello! I ' m John Smith from Infotech.pl/sql procedure successfully completed

To declare a fixed-length string, use the char data type. There is no need to specify a variable with a maximum length of fixed length. If the length of the limit is exceeded, the Oracle database automatically uses the maximum length that is required. So, the following two declarations are the same:

Red_flag CHAR(1): =' y ';: =' y ';        

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:

S.N.	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

The following embodiments illustrate some of the above functions and their purpose:

Example 1

DECLAREGreetings VARCHAR2(11) := ' Hello World ';BEGINDbms_output.Put_Line(UPPER(Greetings));Dbms_output.Put_Line(LOWER(Greetings));Dbms_output.Put_Line(Initcap(Greetings)); /* Retrieve the first character in the string */Dbms_output.Put_Line(SUBSTR(Greetings, 1, 1)); /* Retrieve the last character in the string */Dbms_output.Put_Line(SUBSTR(Greetings, -1, 1)); /* Retrieve five characters, starting from the seventh position. */Dbms_output.Put_Line(SUBSTR(Greetings, 7, 5)); /* retrieve the remainder of the string, starting from the second position. */ Dbms_output. ( SUBSTR  (greetings , 2); /* find the location of the first "E" */ Dbms_output.put_line  ( INSTR  (greetings,  ' E ' ); end;                

When the above code is executed at the SQL prompt, it produces the following results:

HELLO Worldhello Worldhello Worldhdworldello world2pl/sql procedure successfully completed.

Example 2

DECLAREGreetings VARCHAR2(30) := ‘...... Hello world ... ';BEGINDbms_output.Put_Line (rtrim (greetings,< span class= "str" > '. '  Dbms_output. (ltrim (greetings ,  '. '  Dbms_output.trim (  "  from Greetings); end;                

When the above code is executed at the SQL prompt, it produces the following results:

...... Hello World, Hello World ..... Hello Worldpl/sql procedure successfully completed.

SQL Record-plsql string