Oracle Common functions

Oracle Common Functions--trunc,to_date,to_char,to_number, SUBSTR,REPLACE,NVL, Trim,wm_concat,upper, Lower,length,add_months ( sysdate,1)
View Oracle Version: SELECT * from V$version

First, trunc function
1, TRUNC (for dates) The date value that the TRUNC function truncates for the specified element.
Its specific syntax format is as follows: TRUNC (DATE[,FMT])
Where: Date value, FMT date format, and the date will be truncated by the specified element format. Ignoring it is truncated by the nearest date.
Here's how this function is used:
Select Trunc (to_date (' 2012-03-23 23:59:59 ', ' yyyy-mm-dd hh24:mi:ss ')) from dual--return date:2012-3-23
Trunc (sysdate, ' yyyy ')--return to the first day of the year.
Trunc (sysdate, ' mm ')--Returns the first day of the month.
Trunc (sysdate, ' d ')--Returns the first day of the current week.

2, TRUNC (for number)
The TRUNC function returns the processed value, and its working mechanism is very similar to the round function, except that the function does not make the corresponding rounding selection processing of the specified fractional number before or after it, and it is truncated altogether.
Its specific syntax format is as follows Trunc (Number[,decimals])
Where number is to be intercepted, decimals indicates that the digits after the decimal point need to be preserved. Optional, ignore it to truncate all the decimal parts
Here's how this function is used:
TRUNC (89.985,2) =89.98
TRUNC (89.985) =89
TRUNC (89.985,-1) =80
Note: The second parameter can be a negative number, which means that the portion of the digits to the left of the decimal point is truncated to 0. Similar to rounding, such as a parameter of 1 is rounded to a very bit, if 1, it is rounded to 10 bits, and so on; If the parameter is set to a negative number, and the digits of negative numbers are greater than the number of bytes of the integer, the return is 0. such as: TRUNC (89.985,-3) =0.

II, To_char (), To_date (), To_number ()
To Char is the conversion of a date or number to a string; To date is the conversion of a string to a date type in the database
To_char
Use the TO_CHAR function to process numbers: to_char (number, ' format '); To_char (Salary, ' $99,999.99 ');
Use the TO_CHAR function to process the date: to_char (date, ' format ');
Select To_number (To_char (sysdate, ' yyyy ')) from dual--year
Select To_number (To_char (sysdate, ' mm ')) from dual--Take month
Select To_number (To_char (sysdate, ' DD ')) from dual--date taken
To_number
Convert characters to numbers using the To_number function: To_number (char[, ' format ')

To_date
Use the To_date function to convert a character to a date: To_date (char[, ' format ')
Select To_date (' 2011-11-5 4:39:57 ', ' yyyy-mm-dd hh24:mi SS ') as Col from dual
Various formats:


Number format format
Date format
9 represents a number
0 Force Display 0
$ place a $ character
L Place a floating local currency symbol
. Show decimal Points
, displays the thousand indicator

Format Control Description
YYYY, YYY, yy represent 4-bit, 3-bit, 2-bit digital years, and the spelling of year
MM Digital month, month full spell, Mon month abbreviation
DD Digital days, day week full spell, DY week abbreviation, AM indicates morning or afternoon
HH24, HH12 12-hour or 24-hour, MI min, ss sec
SP number's spelling, th number ordinal word
"Special characters" if special characters
HH24:MI:SS AM 15:43:20 PM
DD "of" MONTH of October
Ddspth Fourteenth
Format of date ' 1 August-May-84 '

Three, Instr,substr function
The format of the INSTR method is: INSTR (source string, target string, starting position, matching ordinal)
For example: INSTR (' CORPORATE floor ', ' or ', 3, 2), the source string is ' CORPORATE floor ', the target string is ' OR ', the starting position is 3, and the position of the 2nd match is taken.
The default lookup order is left to right. When the starting position is negative, start looking from the right.
So select INSTR (' CORPORATE floor ', ' OR ',-1, 1) the "instring" from dual display result is: 14

The use of the SUBSTR function
Gets the string substr (string, start_position, [length]) that specifies the starting position and length in the string
Such as:
SUBSTR (' This was a test ', 6, 2) return ' is '
SUBSTR (' This was a test ', 6) return ' is a test '
substr (' techonthenet ', -3, 3) return ' Net '
substr (' Techonthenet ',-6, 3) return ' the ' Select substr (' Thisisatest ', -4, 2) value from dual
For example, it is easier to distinguish between these two functions:
Select substr (' Zhang San: class: Male ', InStr (' Zhang San: First Class: Male ', ': ', +) +1,2) as Class, InStr (' Zhang San: one shift: Male ', ': ', 1, 1) +1 start from dual
The result: Class start position
One Class 4

Four, Trim function
The TRIM function in Oracle is used to delete the given string or the specified character in the head or tail of a given number.
The TRIM function has the following form of trim ([leading/trailing/both][match string or numeric][from][string or numeric value to be processed]) here if the leading indicates a string that matches the header from the delete If a trailing is indicated to indicate a string that matches from the end of the deletion, if both is indicated, or if no location is specified, both ends are deleted, and if no match string or numeric value is considered a space, the preceding or trailing spaces are removed.
The Trim function returns a type of VARCHAR2 here are some examples:
Various situations
Example
Results
Indicates that leading represents a string that matches the header from the delete
Select Trim (Leading ' 1 ' from ' 12321tech11 ') from dual;
2321tech11
Indicates that trailing represents a string that is matched from the delete tail
Select Trim (trailing ' 1 ' from ' 12321tech11 ') from dual;
12321Tech
If both is indicated, or if no location is specified, both ends are deleted
Select Trim (' Medium ' from ' Mid-autumn August ') as poetry from dual;
Select Trim (both ' 1 ' from ' 12321Tech 111 ') from dual;

Autumn August
2321Tech
If you do not indicate any matching string or value is considered a space, delete the preceding or trailing spaces
Select Trim (' tech ') as poetry from dual;
Select Trim (' From ' tech ') as poetry from dual;
Select Trim (0 from 7500) from dual;
Tech
Tech
75
LTRIM, RTRIM can not only remove the space, but also remove the specified characters, such as LTRIM (', aaaa ', ', ')

V. Translate and replace
1.translate
Syntax: TRANSLATE (char, from, to)
Usage: Returns the string from which each character appearing in the From is replaced with the corresponding character in to.
If the from is longer than the to string, then the characters that are more than to in the from will be deleted.
One of the three parameters is empty, and the return value will also be a null value.
Example:sql> Select Translate (' ABCDEFGA ', ' abc ', ' Wo ') return value from dual;
return value-------WODEFGW
Analysis: This statement is to convert ' abc ' in ' Abcdefga ' to ' wo ',
Because ' a ' in ' ABC ' corresponds to ' wo ', the ' a ' in ' ABCDEFGA ' is converted to ' W ';
The ' B ' in ' ABC ' corresponds to ' wo ', so ' B ' in ' ABCDEFGA ' is converted to ' o ';
The ' C ' in ' wo ' does not correspond to the character, so the ' abcdefga ' in the ' C ' all deleted;
Simply put, the character in the from is converted to the character corresponding to the position in to, and if the corresponding character is not found in to, the character in the return value is deleted.
In the actual business, it can be used to delete some exception data, such as a field in Table A t_no represents a phone number, and the phone number itself should be a string composed of numbers,
In order to delete the exception data that contains non-numbers, the Translate function is used:
Sql> Delete from A, where length (translate (Trim (a.t_no), ' 0123456789 ' | | a.t_no, ' 0123456789 ')) <> length (Trim (A . t_no));
2.replace
Syntax: REPLACE (char, search_string,replacement_string)
Usage: Converts all string search_string in char to string replacement_string, without matching strings.
Example:
Sql> Select REPLACE (' Fgsgswsgs ', ' FK ', ' J ') from dual; return value from dual;
The result is fgsgswsgs.
Sql> Select REPLACE (' fgsgswsgs ', ' SG ', ' EEERRRTTT ') returns the value from dual;
The result is Fgeeerrrtttsweeerrrttts.
Analysis: In the first example, because there is no string matching ' FK ' in ' Fgsgswsgs ', the return value is still ' fgsgswsgs ';
The second example converts the string ' sg ' in ' fgsgswsgs ' to ' eeerrrttt '.
Summary: To sum up, replace and translate are alternative functions, except that replace is for a string, and translate is for a single character.

VI, Decode () function
The Decode function, which compares the input value to the parameter list in the function and returns a corresponding value based on the input value. The parameter list of a function is a number of sequential pairs consisting of several numeric values and their corresponding result values. Of course, the function also has a default return value if it fails to match any one of the actual argument order pairs successfully.
Unlike other functions in SQL, the Decode function can also recognize and manipulate null values.
Syntax: DECODE (CONTROL_VALUE,VALUE1,RESULT1[,VALUE2,RESULT2 ...] [, Default_result]);
The value to try to process. The decode function compares this value to the subsequent sequence of even orders to determine the return value.
The value1 is a numerical value of the constituent sequence pairs. If the input value matches successfully, the corresponding result is returned. corresponding to an empty return value, you can use the keyword NULL for the corresponding.
RESULT1 is the result value of a constituent sequence pair.
Default_result The default value returned by the function when it fails to match any one of the values.
For example:
Select decode (x, 1, ' x is 1 ', 2, ' X was 2 ', ' others ') from dual
When x equals 1 o'clock, return ' x is 1 '.
When x equals 2 o'clock, return ' x is 2 '.
Otherwise, return others '.
When you compare 2 values, you can use it with the sign () function.
SELECT DECODE (sign (5-6), 1 "is Positive ',-1," is Nagative "," is Zero ") from dual;
Similarly, it can be implemented in case:
SELECT Case sign (5-6)
When 1 Then ' is Positive '
WHEN-1 Then ' is Nagative '
ELSE ' is Zero ' END
From DUAL
In addition, you can use decode in order by.
For example: Table subject, with subject_name columns. It is required to sort by: language, number, and order. At this point, it is very easy to use the decode to complete the requirements.
SELECT * from Subject ORDER by decode (subject_name, ' language ', 1, ' Math ', 2, ' foreign Language ', 3)

SQL SERVER:
SELECT A,
Case when A=1then ' one '
When A=2then ' both '
ELSE ' Other '
END as COL
From Test;

A | COL
---+-------
1 | One
2 | Both
3 | Other
1 SELECT TOP 3 ID, case if id=1 then ' one ' when id=2 then ' and ' ELSE ' other ' END as idname from users;
2 SELECT TOP 3 ID, case ID when 1 then ' one ' is 2 Then ' and ' ELSE ' other ' END as idname from users;
Same as the results.
(Useful Oracle here as SQL Server)

Seven, NVL function
NVL () function (similar to SQL Server's IsNull)
Syntax: 1. NVL (EExpression1, EExpression2)
Parameters: 1. EExpression1, EExpression2
If the eExpression1 evaluates to a null value, NVL () returns EEXPRESSION2.
If the EExpression1 evaluates to a value other than NULL, the EExpression1 is returned.
EExpression1 and EExpression2 can be any data type.
If the result of both EExpression1 and EExpression2 is null, then NVL () returns. Null..
1. Select NVL (a.name, ' empty ') as name from student a joins school B on A.id=b.id
Note: Two parameter types to match
Viii. Wm_concat function
Wmsys.wm_concat (column) composes multiple records into a comma-separated string.
Select Wmsys.wm_concat (ID) AA from TBL
SQL Server-like statements: SELECT @[email protected] + ', ' + ID from TBL ORDER by ID
Ix. Other String functions
1.ASCII
Returns the decimal number corresponding to the specified character;
Sql> Select ASCII (' a ') a,ascii (' A ') a,ascii (' 0 ') zero,ascii (') space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2.CHR
Give an integer and return the corresponding character;
Sql> Select Chr (54740) ZHAO,CHR (+) chr65 from dual;
ZH C
-- -
Zhao A
3.CONCAT
Connect two strings;
Sql> Select Concat (' 020 ', ' 12345678 ') | | ' Turn 11 ' phone from dual;
Phone
----------------
02012345678 ext. 11
4.INITCAP
Returns a string and capitalizes the first letter of the string;
Sql> Select Initcap (' Smith ') Upp from dual;
UPP
-----
Smith
5.RPAD and Lpad (pasting characters)
Rpad pasting characters to the right of a column
Lpad pasting characters to the left of a column
Sql> Select Lpad (Rpad (' Gao ', ' ten, ' * '), +, ' * ') from dual;
Lpad (Rpad (' GAO ', 1
-----------------
gao*******

6.SOUNDEX
Returns a string with the same pronunciation as the given string
Sql> CREATE TABLE table1 (XM varchar (8));
sql> INSERT INTO table1 values (' weather ');
sql> INSERT INTO table1 values (' wether ');
sql> INSERT INTO table1 values (' Gao ');
Sql> Select XM from table1 where Soundex (XM) =soundex (' weather ');
Xm
--------
Weather
Wether

Oracle Common functions