Shulou(Shulou.com)06/01 Report--

-- ASCII code and character conversion

Select chr (65) | | chr (66) | | chr (67) ABC,chr (54678) from dual

Select ascii ('ABC''), ascii ('ABC') from dual

-- get the string length function

Select length ('ABCDE I FGHI') from dual

-- string interception

Select substr ('ABCDE I FGHI',5,2), substr (' ABCDE I FGHI',-5,2) from dual

-- string concatenation

Select concat ('Hello',' Worldwide') From dual

-- string search

Select instr ('this is a testing!', 'testing'), instr ('this is a testing!', 'this is a testing!') from dual

-- letter case conversion

Select upper ('this is a test') from dual

Select lower ('THIS IS A TEST') from dual

Select initcap ('this is a test') from dual

-- sort functions for specified parameters

Select * from productinfo order by nlssort (productname,'NLS_SORT=SCHINESE_PINYIN_M');-- sort by pinyin according to the productname field

-- replace string function

Select replace ('this is a test','tes','resul') from dual

-- string filling function

Select rpad ('test',8,'*rpad'), rpad (' test',15,'*rpad'), rpad ('test',4,'*rpad') from dual

-- A function that deletes a specified character at the beginning and end of a string

Select trim (trailing't 'from' test'), trim ('test') from dual

Select rtrim ('test'), rtrim ('test*ffs','fs*') from dual

Select ltrim ('ftest','f') from dual

-- character set name and ID interchange

Select nls_charset_id ('US7ASCII') from dual

Select nls_charset_name (1) from dual

-- system date and time function

Select to_char (sysdate,'yyyy-MM-dd hh34:mi:ss') from dual

Select systimestamp from dual

-- get the database time zone function

Select dbtimezone from dual

-- add a specified month function to the date

Select to_char (add_months (to_date ('2009-1-30mm yyyy-mm-dd') from dual

-- returns the current session time zone

Select sessiontimezone from dual

-- returns the last day of the specified month

Select last_day (sysdate) from dual

Returns the date of the week after the specified date

Select sysdate,next_day (sysdate,' Monday') from dual

-returns the current date of the time zone in which the session is located

Select sessiontimezone,to_char (current_date,'yyyy-mm-dd hh34:mi:ss') from dual

-- extract a specific part of the specified date

Select extract (year from sysdate) year

Extract (minute from timestamp '2010-6-18 12:23:10') min

Extract (second from timestamp '2010-6-18 12:23:10') sec

From dual

-- get the number of months between two dates

Select months_between (to_date ('2010-7-1,' yyyy-mm-dd')

To_date ('2010-6-1 million,' yyyy-mm-dd'))

From dual

-- time zone time conversion

Select to_char (sysdate, 'yyyy-mm-dd hh34:mi:ss') one

To_char (new_time (sysdate, 'PDT',' EST'), 'yyyy-mm-dd hh34:mi:ss') two from dual

-date rounding, intercept function

Select to_char (round (to_date ('2010-5-1 21 yyyy-mm-dd hh34:mi:ss'))

'yyyy-mm-dd hh34:mi:ss')

From dual

Select to_char (trunc (to_date ('2010-5-1 09 yyyy-mm-dd hh34:mi:ss'))

'yyyy-mm-dd hh34:mi:ss')

From dual

-- string to ASCII type string

Select asciistr ('this is a test!') From dual

-- binary to decimal

Select bin_to_num (1), bin_to_num (1rect 0penny 0), bin_to_num (1rect 1penny 1) from dual

-- data type conversion function

Select cast ('123' as integer) as vhr

Cast (123 as varchar2 (8)) as num

Cast (sysdate as varchar2 (12)) as dt

From dual

-- string and rowid convert each other

Select chartorowid ('AAARXnAABAAAVgggAB') from dual

Select rowidtochar ('AAARXnAABAAAVggAAB') from dual

-- string conversion between note sets

Select convert ('test', 'US7ASCII') from dual

Hexadecimal strings and RAW types are converted to each other

Select hextoraw ('4d') from dual

Select rawtohex ('4D') from dual

Select rawtonhex ('4D') from dual

-- convert numeric values to characters

Select to_char (16.89), to_char (16.89) from dual

Select to_char (sysdate, 'yyyy-mm-dd')

To_char (sysdate, 'hh34:mi:ss')

To_char (sysdate, 'month',' NLS_DATE_LANGUAGE=ENGLISH')

From dual

-- character to date

Select to_char (to_date ('2010-7-1mm month') from dual

Select to_char (to_date ('2010-7-1mm yyyy-mm-dd') from dual

-- string transfer data

Select to_number ('2456.304) from dual

-- full-width turn to half-angle

Select to_single_byte ('This is a Test') from dual

-returns a function whose expression is NULL

Select coalesce (null,9-9 null) from dual

-- excluding specified conditional functions

Select * from productinfo where lnnvl (quantity > 70)

-- replace null value function

Select nvl (null,0) from dual

Select nvl2 ('true',1,3) from dual

-- find the average function

Select avg (productprice) from productinfo group by category

-- ask for the amount of recording

Select count (*) from productinfo

-- maximum and minimum function

Select * from productinfo where productprice= (select max (productprice) from productinfo)

Summation function

Select sum (all quantity), category from productinfo group by category

-- return login name

Select user from dual

-- returns session and context information

Select userenv ('isdba') from dual

Select sys_context ('userenv','session_user') session_user from dual

-- expression matching function

Select productname,quantity,decode (sign (quantity-80), 1 Magi 'sufficient',-1 Magi 'insufficient', 0 Magi 'insufficient') from productinfo

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.