From Ingres Community Wiki

Contents 1 Ingres Function Matrix 2 Methodology: 3 Ingres User Defined Functions - part of Ingres OME 3.1 Documentation 3.2 Examples 3.3 Links 4 Competitive Products 5 Inventory of Functions 6 Example DB procedure 7 INITCAP 7.1 Declaration 7.2 Usage 7.3 Expected Output 8 CHARINDEX 8.1 Declaration 8.2 Usage 9 TRANSLATE

Ingres Function Matrix

This matrix details the functions found in Oracle, MS SQL Server and MySQL databases that have been identified during migrations.

Ingres Servicedesk issues have been raised for some of the functionality to be added to Ingres.

There are some functions that can be replicated using DB Procedures and User Defined Functions (UDF)

The link below to the Oracle page from Microsoft TechNet is a basis to work from http://technet.microsoft.com/en-us/library/bb497070.aspx

Methodology:

1. check what Ingres does already

2. Check if someone has written a procedure or function and put it on the Matrix

3. Write your DP procedure or Function

4. Contribute it to the WIKI

Ingres User Defined Functions - part of Ingres OME

A prototype buildome has been created to assist with the generation of Functionality required to perform POC's.

Documentation

A Powerpoint is provided to guide you through the components

Hints on using buildome and OME
Page with hints on using the buildome script including hints for functions with multiple function instanaces/overloading

Examples

An example tarball holds a working example of adding two functions.

Links

• OME Function List

Competitive Products

DB2 vs Ingres Database Functions | DB2 vs Ingres Functions
DB2 vs Ingres cross reference shows features that are available in DB2 and may or may not be implemented in Ingres

Informix vs Ingres Database Functions | Informix vs Ingres Functions
Informix vs Ingres cross reference shows features that are available in Informix and may or may not be implemented in Ingres

MySQL vs Ingres Database Functions | MySQL vs Ingres Functions
MySQL vs Ingres cross reference shows features that are available in MySQL and may or may not be implemented in Ingres

Oracle vs Ingres Database Functions | Oracle vs Ingres Functions
Oracle vs Ingres cross reference shows features that are available in Oracle and may or may not be implemented in Ingres

PostgreSQL vs Ingres Database Functions | PostgreSQL vs Ingres Functions
PostgreSQL vs Ingres cross reference shows features that are available in PostgreSQL and may or may not be implemented in Ingres

SQL Server vs Ingres Database Functions | SQL Server vs Ingres Functions
SQL Server vs Ingres cross reference shows features that are available in SQL Server and may or may not be implemented in Ingres

Sybase vs Ingres Database Functions | Sybase vs Ingres Functions
Sybase vs Ingres cross reference shows features that are available in Sybase and may or may not be implemented in Ingres

Inventory of Functions

SD	Functions	Description	Oracle	Oracle Example	MS SQL	MS SQL Example	Ingres	Ingres Example	Functionality available in
	INITCAP	Gives uppercase to the title (Title case)	INITCAP	INITCAP ('ingres database') Ingres Database	No equivalent function	n/a	INITCAP(dbp)	See procedure on this page	n/a
139069	INSTR	Used to find the location of a sub string or a character inside a string	INSTR	INSTR ('NORTH CAROLINA','OR', 1) returns 2	CHARINDEX	CHARINDEX ('OR','NORTH CAROLINA', 1) returns 2
	TRANSLATE	Translate a character string. Used for encryption	TRANSLATE	This function in Oracle is used to convert a string into another form. Low level of encryption is done using this.	No equivalent function	n/a	TRANSLATE(dbp)	see procedure on this page

Example DB procedure

INITCAP

Declaration

sql test << END1
drop procedure initcap
\p\g
create procedure initcap (inout t1 varchar(512))
RESULT ROW initcap (initcap varchar(512))
as
declare ctr int;
a1 int;
c1 char(1);
t2 varchar(512);
begin
t1 = lower(rtrim(:t1));
a1 = length(:t1);
ctr= 1;
t1 = upper(left(:t1, 1) ) + right(:t1, :a1 - 1) ;
while :ctr <> :a1 do
select substring(:t1, :ctr, 1) into :c1;
IF c1=' 'or :c1='_'or :c1=','
or:c1='.'or:c1='['or:c1=']'or:c1='/'or:c1='('or:c1=')'
THEN

t2=left(:t1,:ctr) +upper(substring(:t1,:ctr+1, 1))+right(:t1,(:a1-:ctr)-1);

t1 = :t2;

endif;

ctr = :ctr + 1;

endwhile;
RETURN ROW(:t1);
end;
\p\g
END1

Usage

sql test << END2
select * from initcap (t1='hello people_have(no one)to turn/off the,light.i see you\ are back [in]pickle')
\p\g
\q
END2

Expected Output

"Hello People_Have(No One)To Turn/Off The,Light.I See You\\ Are Back [In]Pickle"

CHARINDEX

Declaration

sql test << END1
drop procedure charindex
\p\g
create procedure charindex (in instring varchar(8192) /* Declare length of input string */
, in substring varchar(8192)
, in position integer)
RESULT ROW (integer)
as
declare ctr int;
begin
ctr=0;
IF :position > 1 THEN
SELECT LOCATE(SUBSTR(:instring,:position),:substring) INTO :ctr;
ELSE
SELECT LOCATE(:instring,:substring) INTO :ctr;
ENDIF;
RETURN ROW(:ctr);
end;
\p\g

END1

Usage

sql test << END2
select * from charindex (instring='hello people in the light or shade'
,substring='light'
, position=1)
\p\g
/* Gives 21 because light is at position 21 in the input string */

select * from charindex ('hello people in the light or shade'
,'dark'
, 1)
/* gives error because dark is not in the string*/
\p\g

select * from charindex ('hello people in the light or shade'
,'light'
, 4000)
/* gives error because 4000 is too long */
\p\g

\q
END2

TRANSLATE

(To be supplied.)

A note on translate: Oracle and DB/2 each have a translate() function, but they are different and have incompatible semantics. A server parameter or session attribute "function_compatibility" (Oracle or DB/2) might be a useful thing.