If you know a command in one db and want to know its equivalent in another db than this should prove useful. You can either scan the page manually are use your browser's search function (Ctrl+f).
PLEASE HELP by updating this page. Simply click 'Add New Row' to add new function or click to edit an existing row.
To contact me, email me at adinaronson-at-hotmail-dot-com.
Click on a row to highlight it
Images of the Israeli aggressors jeopardizing peace for humanity (PowerPoint)

  What Equiv_ORACLE Equiv_MSSQL Equiv_DB2 Equiv_MYSQL Equiv_MCKOI
Edit
Delete
upper case UPPER(myfield) UPPER(myfield) UPPER(myfield) UPPER(myfield) UPPER(myfield)
Edit
Delete
Casting TO_Date(char_field,format_mask)
TO_CHAR(date_field,format_mask)
TO_NUMBER(char_field)
TO_CHAR(num_field) 
'Get todays date without hour,minute,second as a date type: to_date(to_char(SYSDATE, 'dd/mm/yyyy') || ' 00:00:00','dd/mm/yyyy HH24:MI:SS')
or
simply SELECT trunc(sysdate) Also acceps CAST(expr as DATATYPE) CAST(expr as type)
CONVERT(char(30), myfield) 
select convert(varchar(20),getdate(),120) will give the date in YYYY-MM-DD HH24:MI:SS format 
Also accepts CAST(expr as type)
CAST(myfield AS DECIMAL)

CAST(expr as type) in general
CAST(expr AS type) CAST(myfield AS DECIMAL)
Edit
Delete
Getting first 50 rows only select * from myTable WHERE rownum<50
NOTE: select * from myTable WHERE rownum<50 ORDER BY col
will select the first 50 rows, then order those 50
SELECT TOP (50) * FROM myTable
NOTE: SELECT TOP (50) * FROM myTable ORDER BY col
will order the entire matching dataset, then pick the top 50
select * from myTable fetch first 50 rows only select * from myTable LIMIT 50 none (Use Statement.setMaxRows() in your Java code)
Edit
Delete
Altering a table ALTER TABLE <table name> ADD/MODIFY 
(<column name> <data type> (<size),....)
ALTER TABLE <table name>
SET
ALTER TABLE myschema.mytable ALTER COLUMN
mycolumn SET DATA TYPE DECIMAL (15, 0) ;
ALTER TABLE table_name
MODIFY
ALTER CREATE TABLE mytable
( col1 DECIMAL(3,0) NOT NULL UNIQUE,
col2 NUMERIC,
col3 VARCHAR(3) ) 
(Whole table needs to be redefined 
 even when changing only one column, 
 data will not be erased unless a 
 row is droped)
Edit
Delete
Getting first 50 rows only select * from myTable WHERE rownum<=50000000000000000 SELECT TOP (50) * FROM myTable select * from myTable fetch first 50 rows only select * from myTable LIMIT 50 none (Use Statement.setMaxRows() in your Java code)
Edit
Delete
Get next value of an autoincremented fie create sequence seq_name 
minvalue 1
start with 1
increment by 1
cache 20;

...
...

select seq_name.nextval from dual;
Don’t require sequence in MS SQL Server, data type int identity(1,1) will fulfill the functionality.  

But you can still get the next value for an identity column by using this statement:
select convert(int,last_value) + 1 as NEXT_ID 
from sys.identity_columns 
where name = '<Column Name>' 
and object_id = (select s.id from sysobjects s where s.name = '<Table Name>')
INTEGER (data type of the column) NOT NULL GENERATED {ALWAYS|BY DEFAULT} AS IDENTITY

or

create sequence seq_name 
minvalue 1
start with 1
increment by 1
cache 20;
...
...
select next value for seq_name from sysibm.sysdummy1;
INTEGER(data type of the column) NOT NULL AUTO_INCREMENT SELECT NEXTVAL('mytable') as num
Edit
Delete
Replace select Replace(MyTable.MyField, TextToFind, NewText) as x 
from MyTable
select Replace(MyTable.MyField, TextToFind, NewText) as x 
from MyTable
REPLACE(source-string, search-string, replace-string) REPLACE(text_to_operate_on, search_text, replace_text)
Edit
Delete
Concat 'hello' || 'World'
Also accepts CONCAT('a','b')
'hello' + 'World'
If datatypes are different:
RTRIM(CAST(myfield AS varchar(100))) + 'My text'
'hello' || 'World'
Also accepts CONCAT('a','b')
CONCAT('hello', ' ', 'world)
Edit
Delete
system date sysdate
Also accepts ANSI-Standard CURRENT_DATE
GETDATE() or CURRENT_TIMESTAMP CURRENT_DATE CURRENT_DATE
Edit
Delete
assuring value isn't null NVL(value_that_could_be_null, replacement)
also accepts ANSI standard
COALESCE(val,replacement).
ISNULL(value_that_could_be_null, replacement)
also accepts ANSI standard
COALESCE(val,replacement)
COALESCE(value_that_could_be_null, replacement)
Also accepts VALUE and NVL (in DB2 9.5)
IFNULL(value_that_could_be_null, replacement)
Edit
Delete
describe table DESC table_name sp_help table_name DESCRIBE TABLE schema.table DESC table_name
Edit
Delete
recommended links Documentation for 10gR2
http://www.oracle.com/pls/db102/portal.portal_db?selected=3
General repository of Oracle knowhow
http://asktom.oracle.com
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.asp

http://www.devguru.com/technologies/t-sql/home.asp
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp
http://www.ibm.com/developerworks/data
Edit
Delete
Rename table column ALTER TABLE tablename RENAME COLUMN old_columnname TO new_columnname EXECUTE sp_rename N'TableName.ColNameOld', N'ColNameNew', 'COLUMN' ALTER TABLE tablename CHANGE COLUMN old_columnname new_columnname column_definition
Edit
Delete
change default database/schema ALTER SESSION SET CURRENT_SCHEMA=<schemaname> GRANT IMPERSONATE ON USER::User2 TO User1;
EXECUTE AS USER = 'User2';
CONNECT TO <database> USER <username> USING <password>
connect to a database
SET CURRENT SCHEMA = someschema;
Sets the schema. After CONNECT the schema is set to <username>

To super-use to another user (requires privileges):
SET SESSION_USER = <someuser>
use <schemaname>
Edit
Delete
View contents of Stored Procedure select text
from user_source
where type = 'PROCEDURE'
order by name, line;

---
select 'Trigger: ' || trigger_name,
trigger_body
from user_triggers
order by trigger_name;
---
select text
from user_source
where type in ('PACKAGE', 'PACKAGE BODY')
order by name, type, line;
---
SELECT DBMS_METADATA.GET_DDL('PROCEDURE','blah') from DUAL
sp_helptext MyStoredProcedure select ROUTINENAME,TEXT
from syscat.routines
where TEXT is not null

select trigname, text 
from syscat.triggers
DESCRIBE ??
Edit
Delete
Insert value into variable in SP or Func SELECT MAX(ID)+1 INTO myVar FROM MyTable [email protected]= MAX(ID)+1 FROM MyTable SET myVar = (SELECT MAX(ID)+1 FROM MyTable);
or (in compiled routines):
SELECT MAX(ID)+1 INTO myVar FROM MyTable;
SELECT MAX(idField)[email protected];
Edit
Delete
ALTER TABLE table_name drop column colum alter table TABLE drop column COLUMN ALTER TABLE DROP COLUMN {colname} alter table TABLE drop column COLUMN
Edit
Delete
New GUID select sys_guid() from dual select newid() select uuid()
Edit
Delete
Isnull equivalent for SQL Server NVL(express,value if true) Isnull(express,value if true) value(express,value if true) ifnull(express,value if true)
Edit
Delete
sysobjects select * from dba_objects; select * from sysobjects or from 2005 select * from sys.objects
or to follow the ANSI standards select *
from information_schema.tables
list tables show tables
Edit
Delete
how to delete one column from table? Alter table table_name drop column column_name ALTER TABLE table_name DROP COLUMN column_name ALTER TABLE table_name DROP COLUMN column_name ALTER TABLE table_name DROP COLUMN column_name
Edit
Delete
How to get all columns for a TABLE DESC table_name
or
Describe table_name
sp_help {table}
or select * from information_schema.tables
DESCRIBE TABLE schema.table DESCRIBE table;
Edit
Delete
Query Plan EXPLAIN PLAN FOR
SELECT....

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
SET SHOWPLAN_ALL ON

SELECT....

SET SHOWPLAN_ALL OFF
EXPLAIN PLAN FOR SELECT ...
From shell:
db2exfmt -d dbname -o output.txt -1
Edit
Delete
unique row-id All row_id's are unique

eg: select row_id from emp
RRN(tablename)
Edit
Delete
How to show columns whose value is not 0 select sal from emp where sal != 0 select sal from emp where sal <> 0
Edit
Delete
sp_helptext set echo off verify off term on feedback off pagesize 0 long 20000
select text from user_views
where view_name = 'View Name';
sp_helptext View
Edit
Delete
right outer join select e.ename,e.sal,e.job,d.dno,d.dname,d.loc
from emp e, dept d
where e.dno(+) = d.dno

or

select e.ename,e.sal,e.job,d.dno,d.dname,d.loc
from emp e right join on e.dno = d.dno
select e.ename,e.sal,e.job,d.dno,d.dname,d.loc
from emp e, dept d
where e.dno*=d.dno

or

select e.ename,e.sal,e.job,d.dno,d.dname,d.loc
from emp e right outer join on e.dno = d.dno
select e.ename,e.sal,e.job,d.dno,d.dname,d.loc 
from emp e right join d on e.dno = d.dno
Edit
Delete
Between two dates select * from Access_Request WHERE TO_DATE('9/05/2008','MM/DD/YYYY') <= create_date AND 
TO_DATE('9/12/2008','MM/DD/YYYY') >= create_date
select * from Access_Request 
WHERE create_date BETWEEN '2008-09-5' AND '2008-09-12'
select * from Access_Request WHERE create_date BETWEEN '09/05/2008' AND '09/12/2008'
Edit
Delete
Describe Tables desc user_tables sp_help MaterialsRequestPlan
Edit
Delete
sp_helpindex sp_helpindex table_name
Edit
Delete
Convert to String convert(varchar, value)
Edit
Delete
CASE CASE WHEN X = A1 THEN R1 
     WHEN X = A2 THEN R2
     [...]
ELSE RO
END;

or 

DECODE(X,A1,R1,A2,R2,[...],RO)
CASE WHEN FIELD = EXPRESION THEN FIELD/EXPRESION 
     WHEN  FIELD = EXPRESION2 THEN FIELD/EXPRESION
     ELSE FIELD/EXPRESION
END
Edit
Delete
Place Null Value as Default in SELECT SELECT CAST (NULL as Char(10))  as TestFlag FROM TESTDB
Edit
Delete
CHARINDEX Searches expression2 for expression1 and returns its starting position if found. The search starts at start_location.

CHARINDEX(EXPRESSION 1,EXPRESSION2)
Searches expression2 for expression1 and returns its starting position if found. The search starts at start_location.


LOCATE(EXPRESSION 1,EXPRESSION2)
Edit
Delete
SCOPE_IDENTITY() SCOPE_IDENTITY() SYSIBM.IDENTITY_VAL_LOCAL()
Edit
Delete
Create a copy of a table CREATE TABLE t2 AS SELECT * FROM t1 /* data and structure */ SELECT * INTO t2 FROM t1 /* data and structure */ CREATE TABLE t2 ( LIKE t1 ) /* Only structure */
Edit
Delete
sp_ahabal execute dbms.ahabal_and_gawwad sp_ahabal_and_gawwad
Edit
Delete
sp_ahabal execute dbms.ahabal_and_gawwad sp_ahabal_and_gawwad -- -- --
Edit
Delete
Update statement MERGE INTO Table1 A
USING Table2 B ON (B.Col1=A.Col1)
WHEN MATCHED THEN UPDATE SET A.Col2=B.Col2;
UPDATE A SET Col2=B.Col2
FROM Table1 A
Join Table2 B On B.Col1=A.Col1;
Edit
Delete
Update statement MERGE INTO Table1 A
USING Table2 B ON (B.Col1=A.Col1)
WHEN MATCHED THEN UPDATE SET A.Col2=B.Col2;
UPDATE A SET Col2=B.Col2
FROM Table1 A
Join Table2 B On B.Col1=A.Col1;
Edit
Delete
Will To_Number work in mysql and sql?


eXTReMe Tracker