Oracle functions: lpad nvl upper replace

Lpad
In Oracle/PLSQL, the lpad function fills the string on the left with some specific characters,
The syntax format is as follows:
Lpad (string1, padded_length, [pad_string])
String1 is the string to be pasted
Padded_length is the number of returned strings. If the number is shorter than the length of the original string, the lpad function splits the string into padded_length;

Pad_string is an optional parameter. This string is to be pasted to the left of string1. if this parameter is not written, the lpad function will paste a space on the left of string1.
For example:
Lpad ('tech ', 7); will return 'tech'
Lpad ('tech ', 2); will return 'te'
Lpad ('tech ', 8, '0'); will return 'technical tech'
Lpad ('tech on the net', 15, 'z'); will return 'tech on the net'
Lpad ('tech on the net', 16, 'z'); returns 'ztech on the net'
 
 
Nvl
Obtain the total value of a field through the query. If this value is null, a default value is provided.

For example:

Select nvl (sum (t. dwxhl), 1)

From tb_jhde t

It indicates that if sum (t. dwxhl) = NULL, 1 is returned.

Another useful method

Declare I integer

Select nvl (sum (t. dwxhl), 1) into I from tb_jhde t where zydm =-1 so that the obtained total value can be stored in variable I, if the queried value is null, set its value to the default value of 1.

Obtain the total value of a field through the query. If this value is null, a default value is provided.
Select nvl (sum (t. dwxhl), 1) from tb_jhde t where zydm =-1
Nvl (arg, value) indicates that if the previous arg value is null, the returned value is the following value.

Another useful method
Declare
I integer
Select nvl (sum (t. dwxhl), 1) into I from tb_jhde t where zydm =-1
In this way, the obtained total value is stored in variable I. If the queried value is null, the value is set to the default value of 1.

Upper ()
Converts lowercase letters in a string to uppercase letters.
Syntax Upper (string)
String: String that converts lowercase letters to uppercase letters. If the function is successfully executed, a string after converting lowercase letters to uppercase letters is returned. If an error occurs, an empty string ("") is returned (""). If the value of the string parameter is NULL, the Upper () function returns NULL.
Eg: select upper ('acdd') from dual;
UPPER ('acdd ')

Replace Function
Description
Returns a string in which the specified substring has been replaced with another substring and the number of times it has been replaced is also specified.

Syntax

Replace (expression, find, replacewith [, start [, count [, compare])
The Replace function syntax includes the following parts:
Partial description
Expression is required. String expression that contains the substring to be replaced.
Find is required. The substring to be searched.
Replacewith is required. The substring to replace.
Optional. Start position of the substring search in the expression. If this parameter is ignored, start from 1.
Optional. The number of times the substring is replaced. If ignored, the default value is-1, which indicates all possible replacements.
Compare is optional. Numeric value, indicating the comparison method used to determine the substring. For more information about the value, see "set value.

Set Value
The compare parameter settings are as follows:
Constant Value description
VbUseCompareOption-1 uses the set value of the Option Compare statement for comparison.
VbBinaryCompare 0 performs binary comparison.
VbTextCompare 1.
VbDatabaseCompare 2 is only used for Microsoft Access. Perform comparison based on your database information.

Return Value

The return value of Replace is as follows:
If Replace returns a value
Expression is a zero-length string ("").
Expression is Null.
Find a duplicate with a length of zero expression.
Replacewith is a duplicate with a length of zero expression, in which all the available find strings are deleted.
Start> Len (expression) is a string of zero length.
Count is a duplicate of 0 expression.

Description

The Return Value of the Replace function is a string, but a substring from the position specified by start to the end of the expression string has been replaced.
It is not a copy of the original string from start to end.