Single logging function in SQL
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 a zero space ------------------------- --------- 65 97 48 32
2.chr gives an integer, returns the corresponding characters; SQL> SELECT CHR (54740) ZHAO, CHR (65) chr65 from dual;
ZH C - Zhao A
3. Concat connection two strings; SQL> SELECT Concat ('010 -', '88888888') || 'Turn 23' Gao Dynasty Board Phone from Dual;
Gao Dynasty boat phone ---------------- 010-88888888 turn 23
4. Initcap Returns the string and turn the first letter of the string to uppercase; SQL> SELECT INITCAP ('Smith') UPP from DUAL;
UPP ----- Smith
5.instr (C1, C2, I, J) Search for specified characters in a string, returns the position of the specified character; C1 Searched String C2 wants to search for the start position of the search, default 1J's position, default is 1SQL> SELECT INSTR ('Oracle TRANING', 'RA', 1, 2) Instring from Dual;
Instring --------- 9
6.Length Returns the length of the string; SQL> SELECT NAME, Length (Name), Addr, Length (Addr), Sal, Length (to_CHAR (SAL)) from GAO.NCHAR_TST;
Name Length (name) Addr Length (AddR) Sal length (to_char (sal)) ------------------------------ - - ------------ ---------------------------- High-drying 3 Beijing Sea Ingot 6 9999.99 7
7.Lower Returns the string and puts all the characters lowercase SQL> Select Lower ('aabbccdd') Aabbccdd from Dual;
Aabbccdd -------- Aabbccdd
8.upper returns a string and put all the characters to SQL> SELECT UPPER ('AABBCCDD') Upper from Dual;
Upper -------- Aabbccdd
9.RPAD and LPAD (Paste Character) RPAD Paste Character LPAD on the Right of the Column Paste Characters SQL> SELECT LPAD (RPAD ('GAO', 10, '*'), 17, '*') from Dual;
LPAD (RPAD ('Gao', 1 ---------------- ******* GAO ****** is not enough to fill it with *
10.Ltrim and Rtrimltrim Remove the string RTRIM on the left RTRIM Delete the string SQL> SQL> SELECT LTRIM (RTRIM ('GAO Qian Jing', ''), '') from DUAL;
LTRIM (RTRIM ('----------- Gao Qian Jing11.substr (String, Start, Count) Subsidence, starting from Start, take count SQL> SELECT SUBSTR (' 13088888888 " , 3, 8) from Dual;
SUBSTR ('-------- 08888888
12.Replace ('string', 's1', 's2') String Write the character string S2 to be replaced with the string S2 to be replaced SQL> SQL> SELECT Replace ('He Love You', 'He' , 'i') from dual;
Replace ('H ---------- i love you
13. Soundex returns the same string SQL> Create Table Table1 (XM VARCHAR (8)); SQL> Insert Into Table1 Values ('Wether); SQL> Insert Into Table1 Values '); SQL> INSERT INTO TABLE1 VALUES (' GAO ');
SQL> SELECT XM from Table1 Where Soundex (XM) = Soundex ('Weather');
XM -------- Weatherwether
14.Trim ('s' from 'string') Leading Cut the previous character trailing cut off the following characters If not specified, the default is space
15. Sps Returns the absolute value of the specified value SQL> SELECT ABS (100), ABS (-100) from dual;
ABS (100) ABS (-100) ----------------- 100 100
16. Acon gave a string value SQL> SELECT ACOS (-1) from Dual;
ACOS (-1) --------- 3.1415927
17.aSIN gives the value of an anti-sicrosive string SQL> SELECT Asin (0.5) from Dual;
Asin (0.5) ---------. 52359878
18.atan Returns an aromatic cutting value of a number SQL> SELECT Atan (1) from DUAL;
Atan (1) ---------. 78539816
19.ceil returns the minimum integer SQL> SELECT CEIL (3.1415927) from the given number.
CEIL (3.1415927) --------------- 4
20.COS Returns a given number cosine SQL> SELECT COS (-3.1415927) from dual;
COS (-3.1415927) --------------- -1
21.Cosh Returns a digital anti-hosh value SQL> SELECT COSH (20) from DUAL;
Cosh (20) --------- 242582598
22.EXP Returns a number E N - square root SQL> SELECT Exp (2), Exp (1) from dual;
Exp (2) exp (1) ----------------- 7.3890561 2.7182818
23.Floor Take an integer SQL> SELECT FLOOR (2345.67) from DUAL;
Floor (2345.67) -------------- 2345
24.Ln Returns a number of logs SQL> SELECT LN (1), LN (2), LN (2.7182818) from dual; ln (1) ln (2) ln (2.7182818) --------- --------------------- 0 .69314718 .99999999
25.log (N1, N2) Returns a log (2, 4) from DUAL in N1 N2 N2.
LOG (2, 1) log (2, 4) ----------------- 0 2
26.MOD (N1, N2) Returns a N1 divided by N2 SQL> SELECT MOD (10, 3), Mod (3, 3), MOD (2, 3) from DUAL;
MOD (10, 3) MOD (3, 3) MOD (2, 3) ------------------------- 1 0 2
27.power Returns N2 N2 Square SQL> SELECT POWER (2, 10), Power (3, 3) from DUAL;
Power (2, 10) Power (3, 3) -------------------- 1024 27
28.Round and Trunc Follow SQL> SELECT ROUND (55.5), Trunc (-55.5) from the specified precision.
Round (55.5) trunc (55.5) Trunc (-55.5) ----------------------------- - ------------ 56 -55 55-55
29.Sign Number N symbol, more than 0 returns 1, less than 0 Return -1, equal to 0 Returns 0SQL> SELECT SIGN (123), SIGN (-100), SIGN (0) from DUAL;
Sign (123) SIGN (-100) SIGN (0) ------------------ --------- 1 -1 0
30.Sin Returns a number of sinusoids SQL> SELECT SIN (1.57079) from DUAL;
Sin (1.57079) ------------ 1
31.Sigh Returns the value of a diverse sine SQL> SINE (20), SINH (20) from dual;
Sin (20) SINH (20) -----------------. 91294525 242582598
32.SQRT Returns the root SQL> SELECT SQRT (64), SQRT (10) from Dual;
SQRT (64) SQRT (10) ---------- --------- 8 3.1622777
33.Tan Returns the node of the number SQL> SELECT TAN (20), TAN (10) from Dual;
Tan (20) Tan (10) ---------- --------- 2.2371609.64836083
34.Tanh Returns the Digital N of the Double Corruption Value SQL> SELECT TANH (20), TAN (20) from DUAL;
Tanh (20) Tan (20) ---------- --------- 1 2.2371609
35.Trunc intercepted a number of SQL> SELECT TRUNC (124.1666, -2) Trunc1, Trunc (124.16666, 2) from DUAL in accordance with the specified accuracy
Trunc1 Trunc (124.16666, 2) -------------------------- 100 124.1636.Add_months Add or minus the month SQL> SELECT TO_CHAR (Add_MontHS To_date ('199912', 'YYYMM'), 2), 'YYYYMM') from DUAL
TO_CHA ------ 200002sql> select to_char (add_months (to_date ('199912', 'YYYMM'), - 2), 'YYYYMM') from DUAL
TO_CHA ------ 199910
37.Last_day returns the last day of the date SQL> select to_char (sysdate, 'yyyy.mm.dd'), to_char (sysdate) 1, 'YYYY.MM.DD') from DUAL;
TO_CHAR (Sy to_Char ((S -------- ---------- 2004.05.09 2004.05.10sql> select last_day (sysdate) from DUAL
Last_day (s ---------- 31-50-04
38.Months_between (Date2, Date1) gives Date2-Date1 month SQL> SELECT MONTHS_BETWEEN ('19 - 12 month -1999', '19- March-1999') Mon_between from Dual;
MON_BETWEEN ----------- 9SQL> SELECTMONTHS_BETWEEN (TO_DATE ('20005.20'), to_date ('2005.05.20', 'YYYY.MM.DD')) Mon_betw from dual;
Mon_betw --------- -60
39.New_Time (Date, 'this', 'That') gives the date and time SQL> select to_char (sysdate, 'yyyy.mm.dd HH24: MI: SS') BJ_TIME, TO_CHAR (TO_CHAR) New_Time 2 (sysdate, 'pdt', 'gmt'), 'YYYY.MM.DD HH24: MI: SS') LOS_ANGLES from Dual
BJ_TIME LOS_ANGLES ------------------------------------- 2004.05.09 11:05:32 2004.05. 09 18:05:32
40.NEXT_DAY (Date, 'Day') gives the date Date and the week after calculation of the next week SQL> SELECT NEXT_DAY ('18 - May -2001 ',' Friday ') Next_day from Dual;
Next_day ---------- 25-May -01
41.sdate is used to get the current date SQL> select to_char (sysdate, 'dd-mm-yyyy day') from DUAL;
TO_CHAR (sysdate, '----------------- 09-05-2004 Sunday Trunc (Date, FMT) is truncated according to the requirements given, if FMT =' mi 'is represented Reserved, cut off second SQL> select to_char (trunc (sysdate, 'hh'), 'YYYY.MM.DD HH24: MI: SS') HH, 2 TO_CHAR (Trunc (Sysdate, 'Mi'), 'YYYY.MM .dd hh24: mi: ss') hhmm from dual; hh hhmm ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --- 2004.05.09 11:00:00 2004.05.09 11:17:00
42.ChartorowID converts the character data type to RowID type SQL> SELECT ROWID, ROWIDTOCHAR (ROWID), ename from scott.emp;
Rowid Rowidtochar (Rowid) ENAME ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --Aaaafkaacaaaaaaaa Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaad Jones
43.convert (c, dset, sset) converts the source string SSET from a language character set to another Dset character set SQL> SELECT Convert ('strutz', 'WE8HP', 'F7DEC') "Conversion" from DUAL ;
CONVER ------ Strutz
44.Hextoraw converts a hexadecimal string to binary
45.RawTohext converts a binary string to a hexadecimal
46.RowidTocha converts the ROWID data type to a character type
47.to_char (date, 'format') SQL> Select to_char (sysdate, 'yyyy / mm / dd hh24: mi: ss') from DUAL
TO_CHAR (sysdate, 'yy ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
48.to_date (string, 'format') Transforms a string into a date in Oracle
49.to_multi_byte transforms the single-byte character in the string into multi-byte character SQL> SELECT TO_MULTI_BYTE ('High') from DUAL;
TO - high
50.to_Number converts the characters given to Digital SQL> SELECT TO_NUMBER ('1999') Year from Dual;
Year --------- 1999
51.BFileName (Dir, file) Specifies an external binary file SQL> INSERT INTO File_TB1 VALUES (BFilename ('LOB_DIR1', 'Image1.gif'));
52.convert ('x', 'desc', 'Source') Convertion Source Source Source Source Source Source to Desql> Select Sid, Serial #, Username, Decode (Command, 2 0, 'None', 3 2, 'INSERT', 4 3, 5 'SELECT', 6 6, 'UPDATE', 7 7, 'DELETE', 8 8, 'Drop', 9 'Other') CMD from V $ Session Where Type! = 'Background' SID Serial # Username CMD -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- ------ 1 1 None 2 1 None 3 1 None 4 1 None 5 1 None 6 1 None 7 1275 None 8 1275 None 9 20 Gao SELECT 10 40 GAO NONE
53.DUMP (s, fmt, start, length) DUMP function to specify the internal number format fmt returns a value of type VARCHAR2 SQL> col global_name for a30SQL> col dump_string for a50SQL> set lin 200SQL> select global_name, dump (global_name, 1017, 8, 5) dump_string from global_name;
GLOBAL_NAME DUMP_STRING -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------Oracle.World Typ = 1 LEN = 12 CHARACTERSET = zHS16GBK: W, O, R , L, D
54.Empty_blob () and EMPTY_CLOB () These two functions are functions used to initialize the big data type fields.
55.GreateSt Returns the maximum value in a set of expressions, namely the code size. SQL> SELECT GRETEST ('AA', 'AB', 'AC') from DUAL;
Gr - acsql> SELECT GREATEST ('ah', 'An', 'Day') from Dual
GR - day
56.Least returns the minimum of the minimum SQL> SELECT Least ('ah ",' 天 ') from Dual; LE - ah
57.uID Returns the only integer integer SQL> show useruser for the current user "Gao" SQL> SELECT UserName, user_id from dba_users where user_id = uid;
Username user_id --------------------------------------- GAO 25
58.User returns the current user's name SQL> SELECT User from Dual;
User ---------------------------- GAO
59.UseRevn Returns the information of the current user environment, Opt can be: entryid, sessionid, terminal, isdba, lable, language, client_info, lang, vsizeisdba View whether the current user is DBA if it returns Truesql> SELECT Userenv ('isdba') From Dual;
Useren ------ Falsesql> Select Useerenv ('Isdba') from DUAL;
Useren ------ TRUESSSION Returns Session Sign SQL> SELECT Userenv ('sessionID') from DUAL;
Userenv ('sessionid') -------------------- 152ENTRYID Return to the session population sign SQL> SELECT Userenv ('entryid') from DUAL;
Userenv ('entryid') ------------------ 0Instance Returns the logo of the current instance SQL> SELECT Userenv ('Instance') from Dual;
Userenv ('instance') ------------------ 1Language Returns the current environment variable SQL> SELECT Userenv ('Language') from Dual;
Userenv ('Language') ------------------------------------------------------------------------------------------------------ ----------- Simplified Chinese_China.zhs16gbklang Returns the language of the current environment SQL> SELECT Userenv ('lang') from dual;
Userenv ('lang') -------------------------------------------- -------- ENSTERMINAL Returns the user's terminal or the logo of the machine SQL> SELECT Userenv ('Terminal') from DUAL;
Userenv ('Termina -------------- GAOVSIZE (X) Returns the size of X (bytes) SQL> SELECT VSIZE (User), User from Dual;
VSIZE (User) User ---------------------------------------- 6 SYSTEM
60.avg (DistINCT | All) ALL indicates all values for all values, Distinct is only for different values for different values SQLWKS> Create Table Table3 (XM VARCHAR (8), Sal Number (7, 2)); deal with. SQLWKS> insert into table3 values ( 'gao', 1111.11); SQLWKS> insert into table3 values ( 'gao', 1111.11); SQLWKS> insert into table3 values ( 'zhu', 5555.55); SQLWKS> commit; SQL> select avg (Distinct sal) from gao.table3;
AVG (Distinctsal) ---------------- 3333.33
SQL> SELECT AVG (All sal) from gao.table3;
AVG (Allsal) ----------- 2592.59
61.max (DistINCT | ALL) Ask the maximum, all represents the maximum value of all values, Distinct indicates the maximum value of different values, the same, only one SQL> SELECT MAX (DistINCT SAL) from scott.emp;
Max (Distinctsal) ---------------- 5000
62.min (Distinct | All) Summary, all indicates the minimum value of all values, Distinct indicates the minimum value of different values, the same only one SQL> SELECT Min (all sal) from gao.table3;
Min (allsal) ----------- 1111.11
63.STDDEV (DistINCT | ALL) Seeking standard deviation, ALL indicates that all value standards are poor, and Distinct represents only the standard deviation of different values. SQL> SELECT STDDEV (SAL) from scott.emp;
STDDEV (SAL) ----------- 1182.5032
SQL> SELECT STDDEV (Distinct Sal) from scott.emp;
STDDEV (DistinctSal) ------------------- 1229.951
64.Variance (Distinct | All)
SQL> SELECT VARIANCE (SAL) from scott.emp;
Variance (SAL) ------------- 1398313.9
65.Group BY is mainly used to count the number of SQL> Select Deptno, count (*), SUM (SAL) from scott.emp group by deptno;
Deptno count (*) SUM (SAL) ------------------------- 10 3 8750 20 5 10875 30 6 9400
66.Having Packet Statistical Rehabilitation Conditions SQL> Select Deptno, Count (*), SUM (SAL) from Scott.emp Group by DePtno Having Count (*)> = 5;
Deptno count (*) Sum (SAL) ------------------------- 20 5 10875 30 6 9400SQL> Select Deptno, Count (*) , SUM (SAL) from scott.emp haVing count (*)> = 5 group by deptno; deptno count (*) sum (sal) ----------------- ------- 20 5 10875 30 6 9400
67.Order By is used to sort the results of the query to output SQL> Select Deptno, ENAME, SAL from Scott.emp Order by DePTNO, SAL DESC;
Deptno ENAME SAL -------------------------- 10 King 5000 10 Clark 2450 10 Miller 1300 20 Scott 3000 20 Ford 3000 20 Jones 2975 20 ADAMS 1100 20 Smith 800 30 Blake 2850 30 Allen 1600 30 Turner 1500 30 Ward 1250 30 Martin 1250 30 James 950
Oracle's most common function function classic summary Source: chinaitlab Collection 2004-6-14 10:58:00
* SQL Group Function * S (Num Can Be a Column or Expression)
(Null Values Are Ign * Ored, 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 - VARIANCE OF VALUES
*********************************************************** *****************************
* MiscellaneAous functions: *
************************ ******** ************************* ****************************
Decode (expr, srch1, return1 [, srch2, return2 ...], default] - if no search matches the expression of the default is return,
- OtherWise, The First Search That Matches Will Cause
- The Corresponding Return Value to Be Returned
DUMP (Column_name [, FMT [, start_pos [, length]]])
- Returns An Column Info About A of Returns An Column Info About A
- Format Options: 8 = Octal, 10 = Decimel, 16 = HEX, 17 = CHARACTERS
- Return Typecodes: 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 themallest value of all expressions
NVL (expr1, expr2
- IF EXPR1 IS NOT NULL, IT IS RETURNED, OTHERWISE EXPR2 IS RETURNED
Sqlcode
- Returns SQL Error Code Query, of Last Error. Can Not Be Used Directly in
- Value Must Be Set To Local Variable First
SQlerrm
- Returns Sqlin Query, Error Message of Last Error. CAN NOT BE Used Directly
- 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 theuser 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 INALL ORACLE VERSIONS)
VSIZE (EXPR)
- Returns the number of bytes buy by the expression
- Useful in Selecting Information About Table Space Requirements
************************ ******** ************************* ****************************** SQL DATE FUNCTIONS (DT Represe * NTS 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 Inmonth Containing DT
MONTHS_BETWEEN (DT1, DT2) - RETUDT2 RNS FRACTIONAL VALUE OF MONTHS BETWEEN DT1,
New_Time (DT, TZ1, TZZONE 2 2) - DT = DATE IN TIME ZONE 1, RETURns DATE IN TIME
Next_day (dt, str) - datetc ..) OF FIRST (STR) After DT (Str = 'Monday',
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) - AbsoluteValue of Num
CEIL (NUM) - Smallst 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 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 Num1Power 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 Numtan (NUM) - TANGENT (NUM), NUM in Radians Tanh (NUM) - Hyperbolic Tangent (NUM) Trunc (Num1 [, Num2] - Truncatenum1 to Num2 Decimel Places (Default 0) *********************************************************** ***************************** * String functions, * String Result: *********************************************************** ***************************** (NUM) - ASCIICHARACTER for Num CHR (NUM) - ASCII Character for Num Concat (str1, str2) - str1 controlnated 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 spaces) Pad str1 to length num with str2 (Default LTRIM (Str [, set]) - Remove Set from Leftside of Str (Default Spaces) NLS_INITCAP (STR [, NLS_VAL]) - Same as INITCAP for Different Languages NLS_LOWER (STR [, NLS_VAL]) - Same As Lower FordIfferent Languages Replace (str3, str2 [, str3]) - Replaces Str2 with str3 in str1 - deletes str2 from str1 if str3 is omitted RPAD (STR1, NUM [, STR (Default Spaces) 2]) - Right Pad Str1 ToLength Num with str2 RTRIM (STR [, SET]) Spaces - Remove Set from Right Side of Str (Default Soundex (STR) - Phonetic Representation OF STR Substr (Str, Num2 [, Num1]) - Substring of Str, Starting with Num2, - omitted) Num1 Characters (to end of striff " Substrb (Str, Num2 [, Bytes Num1]) - Same as Substr But Num1, Num2 Expressed in Translate (STR, SET1, SET2) - Replace Set1 in Str with set2 - Truncated if set2 is for than set1, IT Will B 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, start 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 Character Set - Chr_set1 Default Is The Datbase 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]) FMT - Converts expr (date or number) to format specified by 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 IndicatorAd, A.D. AD Indicator CC, SCC CENTURY CODE (SCC Includes Spaceor - 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 Ofyear 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, 1TO 5 (Week 1 Begins 1st Day of the Month) D day of week, 1 to 7 DD days 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 # OFDAYS 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', Ando ON SP Converts 1 To 'One', 2 to 'Two', Ando ON SPTH Converts 1 To 'First', 2 to 'Second', Ando ON FX Fillexact: Uses Exact Pattern mathing FM Fill Mode: Toggles Suppression Of Blanks in Output
