Common functions in Oracle

// Remove the last two characters of the field

Select substr (T. modality, 1, length (T. modality)-2), T. PSI from order_list t where T. PL = 'lss' or T. PL = 'pc'

// Add a suffix after a field

Update product_type t set T. code = Concat (T. code, '-1'), T. name = Concat (T. name, '-1') where T. pcode = 'lss'

 

Substr usage:

Truncates string functions in Oracle.

Syntax:

Substr (string, start_position, [length])

Parameter Analysis:

String

String Value

Start_position (Both 0 and 1 indicate that the starting position of the truncation is the first character.)

Truncates the initial position of a string. If 'number' is input and 'start _ position' is negative, it indicates the number starting from the right of the string.

Length

Number of truncated digits, number type

Here, length is optional. If length is null (that is, if it is left blank), all characters After start_position are returned.

Meaning:

Start from start_position and return the length string.

For more information, see.

Example:

Select substr ('this is a test', 6, 2) from test return 'is'

Substr ('syranmo have a dream',-5, 5) returns 'dream'

 

Concat usage:

 

Concat (string 1, string 2, string 3,...): concatenates string 1, string 2, string 3, and other words.

Note that Oracle's c o ncat () only allows two parameters. In other words, only two strings can be connected at a time.

However, in Oracle, we can use '|' to concatenate multiple strings at a time.

Select Concat (region_name, store_name) from geography
Where store_name = 'boston ';
-- 'Astboston'

 

Select region_name | ''| store_name from geography
Where store_name = 'boston ';
-- 'East Boston'

 

Ltrim usage

 

After ltrim deletes the starting space, a character expression is returned.

Syntax
Ltrim (character_expression)

Parameters
Character_expression

Is a character or binary data expression. Character_expression can be a constant, variable, or column. Character_expression

It must be a data type that can be implicitly converted to varchar. Otherwise, use cast to explicitly convert character_expression.

Return type
Varchar

 

Rtrim usage

 

Rtrim truncates all trailing spaces and returns a string.

Syntax
Rtrim (character_expression)

Parameters
Character_expression

An expression composed of character data. Character_expression can be a constant, variable, or a column of character or binary data

Return type
Varchar

Note
Character_expression must be a data type that is implicitly converted to varchar. Otherwise, use the cast function for digital conversion.

Character_expression.

 

To_number usage

 

Convert a string to a numeric type.

 

 

 

Trunc usage

Syntax:

Trunc (date, [format])

 

Example:

 

-- Oracle trunc () function usage
********************/
1. Select trunc (sysdate) from dual -- 2011-3-18 today's date is 2011-3-18
2. Select trunc (sysdate, 'mm') from dual -- 2011-3-1 returns the first day of the current month.
3. Select trunc (sysdate, 'yy') from dual -- 2011-1-1 return the first day of the current year
4. Select trunc (sysdate, 'dd') from dual -- 2011-3-18 return current year month day
5. Select trunc (sysdate, 'yyyy') from dual -- 2011-1-1 return the first day of the current year
6. Select trunc (sysdate, 'D') from dual -- 2011-3-13 (Sunday) returns the first day of the current week
7. Select trunc (sysdate, 'hh') from dual -- 2011-3-18 14:00:00 current time is
8. Select trunc (sysdate, 'mi') from dual -- 2011-3-18 14:41:00 the trunc () function has no precision in seconds.
********************/
/*
Trunc (number, num_digits)
Number.
Num_digits is used to specify the number to take an integer. The default value of num_digits is 0.
When trunc () function is intercepted, No rounding is performed.
*/
9. Select trunc (123.458) from dual -- 123
10. Select trunc (123.458, 0) from dual -- 123
11. Select trunc (123.458, 1) from dual -- 123.4
12. Select trunc (123.458,-1) from dual -- 120
13. Select trunc (123.458,-4) from dual -- 0
14. Select trunc (123.458, 4) from dual -- 123.458
15. Select trunc (123) from dual -- 123
16. Select trunc (123) from dual --
17. Select trunc (123,-1) from dual -- 120

Replace usage

Replace ('2017/08 ','/','-') ==> 2012-09-08

Translate usage

Translate ('Char ', 'From _ string', 'to _ string ')

The return value of translate replaces each character in from_string with the string after the corresponding character in to_string.

Translate Is a superset of the functions provided by replace. If from_string is longer than to_string, extra characters in from_string instead of to_string will be deleted from Char because they do not have replacement characters. To_string cannot be empty. Oracle interprets the Null String as null, and if any parameter in the Translate Is null, the result is also null. (Translate ('please go away ', 'A', null) ==> null)

For example

Select translate ('123abc', '2dc ', '4e') from dual;

Since the positions of from_string and to_string correspond one to one, 2 corresponds to 4, D corresponds to E, C does not have the corresponding value, so C should be deleted.

Therefore, the 2 character in the example will be replaced with 4,

D. do not replace the string because it does not exist,

C because there is no replacement character, C in the string will be deleted

The result is:

143ab

Initcap ()

Suppose C1 is a string. The function initcap () is to uppercase the first letter of each word, and other letters are converted to lowercase to return.

Words are separated by spaces,Control characters, Punctuation and other non-letter symbols.

Example: Select initcap ('Veni, vedi, vici') Ceasar from dual

The result is Ceasar.

Veni, vedi, vici

Rpad Function

The rpad function fills the string with specified characters from the right.
Rpad (string, padded_length, [pad_string])
String indicates the string to be filled.
Padded_length indicates the length of the character, which is the number of returned strings. If the number is shorter than the length of the original string, the rpad function truncates the string to n characters from left to right;
Pad_string is an optional parameter. This string is to be pasted to the right of the string. If this parameter is not written, the lpad function will paste a space on the right of the string.
For example:
Rpad ('tech ', 7); will return 'tech'
Rpad ('tech ', 2); will return 'te'
Rpad ('tech ', 8, '0'); 'tech0000' will be returned'
Rpad ('tech on the net', 15, 'z'); will return 'tech on the net'
Rpad ('tech on the net', 16, 'z'); will return 'tech on the netz'

SQL> select rpad ('A', 5) | decode ('bbb ', null, '', rpad ('bbb', 8 )) | rpad ('cccc', 12) from dual;
 
Rpad ('A', 5) | decode ('bbb ', NUL
------------------------------
AA BBB CCCC

 

 

Instr Functions

In Oracle/PLSQL, The instr function returns the position of the string to be truncated in the source string. Syntax:
Instr (string1, string2 [, start_position [, nth_appearance]) String1 source string to be searched in this string. String2: the string to be searched in string1. Start_position indicates the position of string1 to start searching. This parameter is optional. If omitted, the default value is 1. The string index starts from 1. If this parameter is positive, it is retrieved from left to right. If this parameter is negative, from right to left, the start index of the string to be searched in the source string is returned. Nth_appearance indicates the number of string2. this parameter is optional. If omitted, the default value is 1. If it is negative, an error is returned.

 

Note:
The index number starts from 1.
If string2 is not found in string1, The instr function returns 0.
Example:
Select instr ('syranmo', 's') from dual; -- return 1
Select instr ('syranmo', 'A') from dual; -- 3 is returned.
Select instr ('syran Mo', 'A', 1, 2) from dual; -- Return
0