oracle sql string functions

modify information, and DML uses statements such as INSERT, select, Update, and delete to manipulate the data contained by database objects. (1). Create a table with an existing table Syntax: CREATE TABLE Select Column_names from eg 1), CREATE TABLE tb_dept as SELECT * from dept; 2), CREATE table tb_dept as select A.deptno, A.dname from dept A; 3), CREATE TABLE tb_dept as SELECT * FROM dept a where a.deptno=10; (2), select a row without duplicates, use the DISTINCT keyword eg, select distinc

('fasdbfasegas', 'fa ', 'my') value from dual 27. Length S: Len, datalength O: Length 28. case-insensitive lower, upper 29. uppercase letters S: No O: Select initcap ('abcd DSAF df') value from dual 30. Left fill space (the first parameter of lpad is space, which is the same as the space function) S: Select space (10) + 'abc' Value O: Select lpad ('abc', 14) value from dual 31. Right fill space (the first parameter of rpad is space, which is the same as the space function) S: Select 'abc' + spa

Concatenate multiple rows of records in the same column in Oracle into a string [SQL] -- raw data -- a 111 -- B 222 -- a 333 -- a 444 -- B 555 -- final result -- a 111*333*444 SELECT L4.L _ TIME, MAX (SUBSTR (L4. group CONTENT, 2) final field value FROM (SELECT L3.L _ TIME, SYS_CONNECT_BY_PATH (L3.L _ CONTENT ,'*') AS group content from (SELECT L2.L _ TIME, L2.L

In a sense, it can also be said that the comparison of common functions of sqlserver and MySQL. Mathematical functions 1. Absolute ValueS: Select ABS (-1) ValueO: Select ABS (-1) value from dual 2. INTEGER (large)S: Select ceiling (-1.001) ValueO: Select Ceil (-1.001) value from dual 3. Round (small)S: Select floor (-1.001) ValueO: Select floor (-1.001) value from dual 4. Round (truncation)S: Select cast (-

digitsSelect substr ('abcde', length ('abcde')-3 + 1) from dual;Get current timeSelect sysdate from dual;Set the current time formatAlter session set nls_date_format = 'dd-mon-yyyyhh: mi: ss ';Query current dateSelect current_date from dual;The next Wednesday of the current date is that daySelect next_day (sysdate, 'weday') from dual;[SQL] view plaincopyConversion functionsConvert to characterSelect to_char (sysdate, 'yyyy-mm-ddhh24: mi: ss') from du

CONNECT by condition is Compound, then only one condition requires the PRIOR operator, although You can have multiple PRIOR conditions. PRIOR evaluates the Immediately following expression for the parent row of the current row in a hierarchical query. 　　PRIORis most commonly used when comparing column values with the equality operator.(The PRIOR keyword can is on either side of the operator.) PRIOR Causes Oracle to use the value of th

) value from dual 81 12. Random Number acquisitionS: Select rand () ValueO: select Sys. dbms_random.value (0, 1) value from dual; 13. Get the symbolS: Select sign (-8) Value-1O: Select sign (-8) value from dual-1 14. Circumference RateS: Select Pi () value 3.1415926535897931O: Unknown 15. Sin, cos, and Tan parameters are in radians.For example, select sin (PI ()/2) value to get 1 (sqlserver) 16. asin, ACOs, atan, atan2 return radians 17. radian angle Interchange (,

1. Query the current date and timeSelect from dual;2. Check the last day of this monthSelect from dual;3. How many months before and after querySelect add_months (sysdate, from dual;4. Check the specific date of the week of the following week (Sunday-Saturday: 1-7)Select next_day (sysdate,1 from dual;5, Time to turn timeSelect to_char (sysdate,'yyyy-mm-dd HH:MI:SS' from dual;6. Character Turn timeSelect to_date ('20160101','yyyy-mm-dd ' from dual;7. The date of the inquiry is specific to the d

: INSERT into Tb_stu (ID, Birthday) VALUES (1, to_date (' 20111130 ', ' YYYYMMDD '));6, clear screen: clear screens;7. Two single quotes in Oracle represent a single quotation markeg, update tb_stu set pwd= ' 1 ' 2 ' where id=1;8. Oracle MatchEg, select * from Tb_stu where pwd like ' A% '--matches multipleSELECT * from Tb_stu where pwd like ' a_ '--matches a9, drop table tb_dept purge; --Permanently deleted

; Parameter description: 1.substr (str, intercept start position, len) //Returns the intercepted Word, right (Str,len) returns the Len string starting from the rightmost 2.to_char (sysdate, ' YYYYMMDD ') //Return to current month day 3.NVL () // NVL (STR1,STR2) If the first parameter of Oracle is empty then the value of the second parameter is displayed, and if the value of the first parameter is not NUL

(SAL),Count (*), COUNT (Job), COUNT (Distinct (job)),Sum (SAL) from the EMP group by DEPTNO;2) Department 30 maximum wage, minimum wage, total number, number of jobs, number of jobs and sum of wagesSelect Deptno, Max (ename), Max (SAL),Min (ename), Min (Sal),AVG (SAL),Count (*), COUNT (Job), COUNT (Distinct (job)),Sum (SAL) from the EMP group by DEPTNO have deptno=30;3.stddev returns the standard deviation of a set of valuesSelect Deptno,stddev (SAL) from the EMP group by DEPTNO;Variance return

value Select min from EMP; Max: Max value Sum: Sum Count: Calculates the number of bars; note: null rows are not counted if the column name is written. Select Count(*) fromEmpwhereJob='MANAGER';--count the number of managers Select sum(SAL) fromEmp--sum of statistical wages Select Count(*) fromEmpwhereDeptno= -;--Statistics Department Number 30, how many people? Select Count(distinctJob fromEmp--How many jobs are there, removing duplicate data Note: The results of the query can not be displa

. Enter and execute the query: SQL code SELECTename, round (sysdate-hiredate) days fromEmp WHEREDeptno = 10; SELECT ename, round (sysdate-hiredate) days from emp WHERE deptno = 10; The result returned is: Ename days --------------- --------------------- CLARK 7913 KING 7752 MILLER 7685 Note: This query uses the subtraction of dates to find the number of days between two dates. The number of days is rounded with the round function. Conversio

) vaule from DUAL;42. String Turn TimeS: can go directly or select cast (\ "2004-09-08\" as DateTime) valueO:select to_date (\ "2004-01-05 22:09:38\", \ "Yyyy-mm-dd hh24-mi-ss\") Vaule from DUAL;43. Find the difference between a part of two dates (e.g. seconds)S:select DateDiff (Ss,getdate (), GETDATE () +12.3) valueO: Subtract directly from two dates (e.g. d1-d2=12.3)SELECT (D1-D2) *24*60*60 vaule from DUAL;44. Date of novelty based on difference (e.

Oracle date functions/character functions/numeric functions/conversion functions/Aggregate functions Oracle date functions: [

Label:Use SQL statements to export stored procedures and functions in Oracle: SETEchoif ; SETHeadingoff ; SETFeedbackoff ; SPOOL'C:/prc.sql' Replace SELECT Case whenLine= 1 Then 'CREATE OR REPLACE' || TEXT whenLine=Max_line Then TEXT ||CHR (Ten)|| '/' ELSE TEXT END fromUser_source A Left JOIN(

Tags: Enter use statement function style to establish a return return statement specificThe tables used in the following test cases are derived from the Scott scenario, before use, make sure that the user is unlocked1. Introductionfunction is used to return a specific data, when a function is established, the function header must contain a return clause, and the function body must contain the data returned by the return statement , we can use the CREATE function to establish the

--Create a functionCreate or Replace function Add_sal (ssal number) return numberIsBegin if (Ssal >) Then return ssal + 51; elsif (Ssal >) Then return ssal + 111; Else return ssal + 222; End If;EndSelect Sal, Add_sal (SAL) from EMP;--TriggerCREATE TABLE Deptlog ( UName varchar2 (20), Action Varchar2 (20), DTime Date);--Create a trigger--for each row can trigger multiple entries, and how many times your language affects how many records will be triggeredCreate

Label:stylecolorsponbs adefsqloracle concat (' Hello ', ' world ') stitching HelloWorld substr (' HelloWorld ', 1,5) intercept Hello Length (' HelloWorld ') string length ten InStr (' HelloWorld ', ' W ') W characters first occurrence 6 Lpad (1200,6, ' * ') If 1200 complements 6 bits, fill ' * ' **1200 on the lef