******************************************************************************* * 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