General SQL Functions - RaspPywriter/SQL-and-BigQuery GitHub Wiki

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

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)