1, Concatconcat (string 1, String 2, String 3, ...): string 1, String 2, String 3, and so on concatenated together.
For example:
Geography form
Region_name Store_name
East Boston
East New York
West Los Angeles
West San Diego
Example 1:
Mysql/oracle:
SELECT CONCAT (region_name,store_name) from Geography
WHERE store_name = ' Boston ';
Results:
' Eastboston ' 2, the use of to_date function in Oracel, used to query the time period. Sql= "SELECT * from Managedate where aaa_date>=" &2005-11-1& "and aaa_date<=" &2005-11-20& "to_date (' 2011-8-22 09:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') 3. Usage of trunc function--oracle trunc () function
/************** Date ********************/
1.select trunc (sysdate) from dual--2013-01-06 today's date is 2013-01-06
2.select trunc (sysdate, ' mm ') from dual--2013-01-01 returns the first day of the month.
3.select trunc (sysdate, ' yy ') from dual--2013-01-01 returns the first day of the year
4.select trunc (sysdate, ' DD ') from dual--2013-01-06 return current month day
5.select trunc (sysdate, ' yyyy ') from dual--2013-01-01 returns the first day of the year
6.select trunc (sysdate, ' d ') from dual--2013-01-06 (Sunday) returns the first day of the current week
7.select trunc (sysdate, ' hh ') from dual--2013-01-06 17:00:00 current time is 17:35
8.select trunc (sysdate, ' mi ') from dual--2013-01-06 17:35:00 trunc () function no seconds accurate
/*************** Digital ********************/
/*
TRUNC (Number,num_digits)
Number requires a truncated rounding.
The num_digits is used to specify the number of rounding precision. The default value for Num_digits is 0.
TRUNC () function is not rounded when truncated
*/
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,1) from dual--12317.select trunc (123,-1) from dual--120 4.
In Oracle/plsql, the sign function returns the positive and negative signs of a number.
The syntax is as follows: sign (number)
Number to test the numbers of the flags.
If Number < 0, then sign returns-1.
If Number = 0, then sign returns 0.
If number > 0, then sign returns 1 5. Query the length () function of the SQL field. 6, decode (condition, value 1, translation value 1, value 2, translation value 2,... The value n, the translation value n, the default value) is understood as follows:
if (condition = = value 1)
Then
return (translation value 1)
elsif (Condition = = value 2)
Then
Return (translation value 2)
......
elsif (Condition = = value N)
Then
return (translated value n)
Else
return (default value)
End If
Note: The default value can be either the column name you want to select, or the other values you want to define, such as other; 5.Lpad () functionThe Lpad function fills the left string with some specific characters
1. The syntax format is as follows:
Lpad (string,n,[pad_string])
Parameter description:
String, or column name.
N: StringTotal length。 If this value is shorter than the length of the original string, the Lpad function will intercept the string as a left-to-right n character;
Pad_string: The string to be populated, the default is padding space.
2. Example
Select Lpad (' Tech ', 7) from dual;
--will return ' tech '
Select Lpad (' Tech ', 2) from dual;
--will return ' TE '
Select Lpad (' Tech ', 8, ' 0 ') from dual;
--will return ' 0000tech '
Select Empno,lpad (ename,7) as ename from EMP;
--will return:
EMPNO ename
Use of special functions in SQL statements
