- 论坛徽章:
- 0
|
▲请教大家两个SQL函数TO_NUMBER() 和 TO_CHAR()
[quote]原帖由 "hills"][/quote 发表：
如果TO_CHAR(参数1，参数2) 的参数1为数值型时，参数2的格式为怎样的？
数字转换为字符串直接: TO_CHAR(参数1) ,第2个参数可以省略。
给你一常用函数（转）
*******************************************************************************
* SQL Group Functions (num can be a column or expression) *
(null values are ignored, default between distinct and all is all) *
*******************************************************************************
AVG([distinct or all] num) average value
COUNT(distinct or all] num) number of values
MAX([distinct or all] num) maximum value
MAX([distinct or all] num) minimum value
STDDEV([distinct or all] num) standard deviation
SUM([distinct or all] num) sum of values
VARIANCE([distinct or all] num) variance of values
*******************************************************************************
* Miscellaneaous Functions : *
*******************************************************************************
DECODE(expr, srch1, return1 [,srch2, return2...], default]
if no search matches the expression then the default is returned,
otherwise, the first search that matches will cause
the corresponding return value to be returned
DUMP(column_name [,fmt [,start_pos [, length]]])
returns an internal oracle format, used for getting info about a column
format options : 8 = octal, 10 = decimel, 16 = hex, 17 = characters
return type codes : 1 = varchar2, 2 = number, 8 = long, 12 = date,
23 = raw, 24 = long raw, 69 = rowid, 96 = char, 106 = mlslabel
GREATEST(expr [,expr2 [, expr3...]]
returns the largest value of all expressions
LEAST(expr [,expr2 [, expr3...]]
returns the smallest value of all expressions
NVL(expr1 ,expr2
if expr1 is not null, it is returned, otherwise expr2 is returned
SQLCODE
returns sql error code of last error. Can not be used directly in query,
value must be set to local variable first
SQLERRM
returns sql error message of last error. Can not be used directly in query,
value must be set to local variable first
UID
returns the user id of the user you are logged on as
useful in selecting information from low level sys tables
USER
returns the user name of the user you are logged on as
USERENV('option')
returns information about the user you are logged on as
options : ENTRYID, SESSIONID, TERMINAL, LANGUAGE, LABEL, OSDBA
(all options not available in all Oracle versions)
VSIZE(expr)
returns the number of bytes used by the expression
useful in selecting information about table space requirements
*******************************************************************************
* SQL Date Functions (dt represents oracle date and time) *
* (functions return an oracle date unless otherwise specified) *
*******************************************************************************
ADD_MONTHS(dt, num) adds num months to dt (num can be negative)
LAST_DAY(dt) last day of month in month containing dt
MONTHS_BETWEEN(dt1, dt2) returns fractional value of months between dt1, dt2
NEW_TIME(dt, tz1, tz2) dt = date in time zone 1, returns date in time zone 2
NEXT_DAY(dt, str) date of first (str) after dt (str = 'Monday', etc..)
SYSDATE present system date
ROUND(dt [,fmt] rounds dt as specified by format fmt
TRUNC(dt [,fmt] truncates dt as specified by format fmt
*******************************************************************************
* Number Functions : *
*******************************************************************************
ABS(num) absolute value of num
CEIL(num) smallest integer >; or = num
COS(num) cosine(num), num in radians
COSH(num) hyperbolic cosine(num)
EXP(num) e raised to the num power
FLOOR(num) largest integer < or = num
LN(num) natural logarithm of num
LOG(num2, num1) logarithm base num2 of num1
MOD(num2, num1) remainder of num2 / num1
POWER(num2, num1) num2 raised to the num1 power
ROUND(num1 [,num2] num1 rounded to num2 decimel places (default 0)
SIGN(num) sign of num * 1, 0 if num = 0
SIN(num) sin(num), num in radians
SINH(num) hyperbolic sine(num)
SQRT(num) square root of num
TAN(num) tangent(num), num in radians
TANH(num) hyperbolic tangent(num)
TRUNC(num1 [,num2] truncate num1 to num2 decimel places (default 0)
*******************************************************************************
* String Functions, String Result : *
*******************************************************************************
(num) ASCII character for num
CHR(num) ASCII character for num
CONCAT(str1, str2) str1 concatenated with str2 (same as str1||str2)
INITCAP(str) capitalize first letter of each word in str
LOWER(str) str with all letters in lowercase
LPAD(str1, num [,str2]) left pad str1 to length num with str2 (default spaces)
LTRIM(str [,set]) remove set from left side of str (default spaces)
NLS_INITCAP(str [,nls_val]) same as initcap for different languages
NLS_LOWER(str [,nls_val]) same as lower for different languages
REPLACE(str1, str2 [,str3]) replaces str2 with str3 in str1
deletes str2 from str1 if str3 is omitted
RPAD(str1, num [,str2]) right pad str1 to length num with str2 (default spaces)
RTRIM(str [,set]) remove set from right side of str (default spaces)
SOUNDEX(str) phonetic representation of str
SUBSTR(str, num2 [,num1]) substring of str, starting with num2,
num1 characters (to end of str if num1 is omitted)
SUBSTRB(str, num2 [,num1]) same as substr but num1, num2 expressed in bytes
TRANSLATE(str, set1, set2) replaces set1 in str with set2
if set2 is longer than set1, it will be truncated
UPPER(str) str with all letters in uppercase
*******************************************************************************
* String Functions, Numeric Result : *
*******************************************************************************
ASCII(str) ASCII value of str
INSTR(str1, str2 [,num1 [,num2]]) position of num2th occurrence of
str2 in str1, starting at num1
(num1, num2 default to 1)
INSTRB(str1, str2 [,num1 [num2]]) same as instr, byte values for num1, num2
LENGTH(str) number of characters in str
LENGTHB(str) number of bytes in str
NLSSORT(str [,nls_val]) nls_val byte value of str
*******************************************************************************
* SQL Conversion Functions *
*******************************************************************************
CHARTOROWID(str) converts str to ROWID
CONVERT(str, chr_set2 [,chr_set1]) converts str to chr_set2
chr_set1 default is the datbase character set
HEXTORAW(str) converts hex string value to internal raw values
RAWTOHEX(raw_val) converts raw hex value to hex string value
ROWIDTOCHAR(rowid) converts rowid to 18 character string format
TO_CHAR(expr [,fmt]) converts expr(date or number) to format specified by fmt
TO_DATE(str [,fmt]) converts string to date
TO_MULTI_BYTE(str) converts single byte string to multi byte string
TO_NUMBER(str [,fmt]) converts str to a number formatted by fmt
TO_SINGLE_BYTE(str) converts multi byte string to single byte string
*******************************************************************************
* SQL Date Formats *
*******************************************************************************
BC, B.C. BC indicator
AD, A.D. AD indicator
CC, SCC Century Code (SCC includes space or - sign)
YYYY, SYYYY 4 digit year (SYYYY includes space or - sign)
IYYY 4 digit ISO year
Y,YYY 4 digit year with comma
YYY, YY, or Y last 3, 2, or 1 digit of year
YEAR, SYEAR year spelled out (SYEAR includes space or - sign)
RR last 2 digits of year in prior or next century
Q quarter or year, 1 to 4
MM month - from 01 to 12
MONTH month spelled out
MON month 3 letter abbreviation
RM roman numeral for month
WW week of year, 1 to 53
IW ISO week of year, 1 to 52 or 1 to 53
W week of month, 1 to 5 (week 1 begins 1st day of the month)
D day of week, 1 to 7
DD day of month, 1 to 31
DDD day of year, 1 to 366
DAY day of week spelled out, nine characters right padded
DY day abbreviation
J # of days since Jan 1, 4712 BC
HH, HH12 hour of day, 1 to 12
HH24 hour of day, 0 to 23
MI minute of hour, 0 to 59
SS second of minute, 0 to 59
SSSSS seconds past midnight, 0 to 86399
AM, A.M. am indicator
PM, P.M. pm indicator
any puctuation punctuation between format items, as in 'DD/MM/YY'
any text text between format items
TH converts 1 to '1st', 2 to '2nd', and so on
SP converts 1 to 'one', 2 to 'two', and so on
SPTH converts 1 to 'FIRST', 2 to 'SECOND', and so on
FX fill exact : uses exact pattern matching
FM fill mode : toggles suppression of blanks in output |
|
免费注册
发表于 2004-11-02 16:31
