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 SELECT @myVar = 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) + 1 FROM mytable INTO @myvar;
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 DATEDIFF(dd, '24 March 2001','24 March 1964')
SELECT DATEDIFF(month, '24 March 2001','24 March 1964')
SELECT DATEDIFF(yyyy, '24 March 2001','24 March 1964') 		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 CARE 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()


eXTReMe Tracker