-
Notifications
You must be signed in to change notification settings - Fork 0
General SQL Functions
The following functions are in SQL and some (or all) of the other DB-specific languages. I am only considering these languages: Oracle, DB2, MySQL, SQL Server, PostgreSQL because those are the languages I have experience in and if no
This statement is used to conditionally insert, update, or delete records in a table depending on whether or not corresponding records exist. If a record exists, then update it, if not, insert it.
merge into emp_commission ec using (Select * for emp) emp on (ec.empno-emp.empno) when matched then update set ec.comm = 1000 delete where (sal < 2000) when not matched then insert (ec.empno,ec.ename,ec.deptno,ec.comm) values(emp.empno,emp.ename,emp.deptno,emp.comm)
The SUBSTR function will return the requested number of characters from a specified start and end position
Languages that have substr function: SQL Server (substring), Oracle, DB2, MySQL, PostgreSQL (substring)
SUBSTR( string, start_position [, length ] )
String: hard-coded or it can be a column value.
Start position: starting position you want captured, you can count from the front of the string (using positive integers so 1 is at the first position, 2 is the second position, and so on. You can also start counting from the end of the string if you use negative integers, so -1 is the last letter, -2 is the second last letter, and so on.
Length: this is an optional argument. You give the number of characters you want returned. If you omit this, you will get back a string that is cut from the start position to the end.
Example: SELECT , from ORDER BY SUBSTR(,length()-2)
Returns the string from the first argument after the characters specified in the second argument are translated into the characters specified in the third argument.
Syntax TRANSLATE(string, characters, translations)