Correspondance des fonctions des divers SGBDR

fadace

Correspondance des fonctions des divers SGBDR en vue d'un portage d'un SGBDR à l'autre. ♪

- Titre : Correspondance des fonctions des divers SGBDR

- Auteur : fadace

- Parution : 22 juillet 2005

- Licence : Copyright ® 2005 Fadace. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Introduction

- II. Fonctions d'agrégat

- III. Fonctions sur chaînes de caractères

- IV. Fonctions temporelles

- V. Fonctions numériques

- VI. Autres fonctions

I - Introduction

Nous avons tous une langue maternelle, et certains chanceux ont des compétences complémentaires dans d'autres langues.

En informatique, la problématique est la même : on apprend un langage avec une certaine peine, mais lorsque la pratique est là, c'est le langage que l'on connaît généralement le mieux. La connaissance d'autres langages est alors plus aisée si l'on peut apprendre « par similitudes ».

L'Esperanto en SGBDR est représenté par les diverses normes SQL. Il y a cependant des différences mineures qui créent des problèmes majeurs aux développeurs censés passer d'un SGBDR à un autre : le langage de chaque SGBDR qui diffère pour écrire les objets compilés (fonctions, triggers, contraintes, procédures stockées), et les fonctions.

Voici donc un petit comparatif qui vous permettra de traduire plus aisément une fonction d'un SGBD à l'autre.

Pour la compatibilité, les acronymes suivants ont été utilisés :

- SQL = norme SQL2 ;

- ASE = Sybase Adaptive Server Enterprise (15) ;

- ASA = Sybase Adaptive Server Anywhere ;

- MS = Microsoft SQL Server (2000) ;

- OR = Oracle (10g) ;

- DB = DB2-UDB (8.2) ;

- AS = DB2-400 (V5R3) ;

- MY = MySQL (4.1) ;

- PG = PostgreSql (8.0) ;

- IN = Informix.

Si d'aventure vous vouliez enrichir cette page avec d'autres SGBDR, n'hésitez pas à me faire parvenir votre matrice de correspondance.

II - Fonctions d'agrégat

Action

Fonction

Compatibilité

Somme des champs non nulls

Select caisse, sum(montant) from ecritures where annee=2005 group by caisse

SQL,OR,DB,AS,ASA,MS,MY

Nombre des champs non nulls

Select caisse, count(montant ) from ecritures group by caisse

SQL,OR,DB,AS,ASA,MS,MY

Maximum

Select max(montant) from …

SQL,OR,DB,AS,MS,ASE,ASA,MY

Minimum

Select min(montant) from …

SQL,OR,DB,AS,MS,ASE,ASA,MY

Moyenne

Select avg(montant) from …

SQL,OR,DB,AS,MS,ASE,ASA,MY

Écart type

Select stddev(montant) from…

OR,DB,AS,MY,PG

Variance

Select var(montant) from…
Select variance(montant) from…

DB,AS,MS
OR,MY,PG

Autres fonctions analytiques

corr, covar_pop, covar_samp, cume_dist, dense_rank, first, first_value, lag, last, last_value,lead,ntile, percent_rank, percentile_cont, percentile_disc, rank, ration_to_report, row_number, stddev_pop, stddev_samp, var_pop, var_samp

OR

III - Fonctions sur chaînes de caractères

Action

Fonction

Résultat

Compatibilité

Concaténation

'A' || 'B'
concat('A','B')
'A' + 'B'
concatenate ('A','B')

'AB'

92,OR,DB,AS,IN,ASA
OR,DB,AS,MY,PG
MS,ASE,ASA
SQL

Conversion en chaîne

(var)char(89)
to_char(89)
convert(char(2), 89), str(89)
cast(89 as char(2))

'89'

DB,AS
OR
MS,ASE,MY
SQL,MS,OR,DB,AS,MY

Transformation ASCII

char(89)
chr(89)
ASCII('Y')

'Y'
'Y'
89

MS,ASE
OR,PG
MS,OR,MY

Longueur d'une chaîne

char[acter]_length('A ')
length('A ')
datalength('A '), len('A ')

3

SQL,DB,AS,MY
OR,DB,AS,MY,ASA,IN,PG
MS,ASE,ASA

Position d'une sous-chaîne

instr('ABCDE', 'CD')
locate('CD', 'ABCD')
patindex('%CD%', 'ABCD')
position('ABCDE', 'CD')

3

OR,MY
DB,AS,MY
MS,ASE
SQL,MY

Mise en majuscules

ucase('BonJour')
upper('BonJour')

'BONJOUR'

DB,AS,MY
SQL,MS,ASE,ASA,OR,DB,AS,MY,PG

Mise en minuscules

lcase('BonJour')
lower('BonJour')

'bonjour'

DB,AS,MY
SQL,MS,ASE,ASA,OR,DB,AS,MY,PG

Remplissage à gauche

lpad('A',5,'X')
replicate('X',5)+'A'

'XXXXXA'

OR,MY
MS,ASE

Remplissage à droite

rpad('A',5,'X')
'A'+replicate('X',5)

'AXXXXX'

OR,MY
MS,ASE

Initiales en majuscules

initcap('de LA molle')
translate('de LA molle')

'De La Molle'

OR
SQL

Suppression des espaces à gauche

ltrim(' A B ')

'A B '

OR,MS,ASE,DB,AS,MY

Suppression des espaces à droite

rtrim(' A B ')

' A B'

OR,MS,ASE,DB,AS,MY

Suppression des espaces à gauche et à droite

trim(' A B ')
ltrim(rtrim(' A B '))

'A B'

SQL,OR,AS,MY
MS,ASE,OR,DB,AS,MY

Conversion en Unicode

nchar(509)

'ae'

MS

Recherche phonétique

soundex('Fabien')

'F150'

OR,MS,ASE,AS,MY

Génération d'espace

space(5)
lpad('',5,' ')

' '

MS,ASE,DB,AS,MY
OR

Extraction d'une sous-chaîne

substr(‘ABCDE',3,2)
substring(‘ABCDE',3,2)

'CD'

OR,DB,AS,MY
SQL,MS,ASE,MY

Transformation de caractères

translate('abcd', 'ac', 'xy')
translate('abcd', 'xy', 'ac')

'xbyc'

SQL,OR
DB,AS

Remplacement d'une chaîne

replace('ABC', 'AB','B')
str_replace('ABC', 'AB','B')

'BC'

MS,DB2,AS,OR,MY
ASE

Remplacement d'une chaîne par positionnement

stuff('abcdef', 2, 3, 'ijklmn')
left('abcdef',1)+ 'ijklmn'+right('abcdef', datalength('abcdef')-4)
instr('abcdef',1)||'ijklmn'||instr('abcdef', -4)

'aijklmnef'

MS
MS,ASE
OR

Renversement d'une chaîne

reverse('ABCD')

'DCBA'

ASE,MS,OR,MY

Partie gauche d'une chaîne

left('ABCD',2)
substring('ABCD',0,2)
substr('ABCD',1,2)

'AB'

MS,DB,AS,MY
ASE,ASA,IN
OR

Partie droite d'une chaîne

right('ABCD',2)
substr('ABCD',-2)

'CD'

ASE,MS,DB,AS,MY
OR

IV - Fonctions temporelles

Action

Fonction

Résultat

Compatibilité

Conversion en date

date('2002-01-02')
to_date('2002-01-02')
cast('2002-01-02' as …)
str_to_date('2002-01-02'…)

'01/02/2002'

AS,DB
OR
SQL,AS,DB,OR,MS,MY,ASA,IN
MY

Ajout d'un intervalle à une date

dateadd(M,2,'01.02.2005')
dateadd, adddate('01.02.2005', INTERVAL 2 MONTH), '01.02.2005'+interval 2MONTH

'1 avr. 2005 0:00'

MS,ASE
SQL,MY,OR,IN,MS,ASA

Retrait d'un intervalle à une date

dateadd(M,-2,'01.02.2005')
date_sub('1.2.2005', INTERVAL 30 DAY)
'1.2.3005' - 2 month

'1 dec. 2004 0:00'

MS,ASE
MY
DB,AS,ASA,IN,MS,OR

Différence entre 2 dates

datediff(day, '1.1.2005', '1.1.2006')
datediff('1.1.2005', '1.1.2006')
days(date('1.1.2005'))-days(date('31.12.2005'))

365

MS,ASE
MY
AS,DB

Nom du jour

dayname('19.7.2005')
datename(d,'19.7.2005')
date_format('19.7.2005', '%W'), dayname('19.7.2005')

'Mardi'

AS,DB
MS,ASE

Dernier jour du mois

last_day('4.2.1996')
dateadd(D,-1,convert(datetime,'1.'+datepart(M,dateadd(M,1,'4.2.1996'))+'.'datepart(Y,'4.2.1996')))

'29.2.1996'

OR,MY
MS,ASE

Conversion en heures

time('01:00')

'01:00:00'

DB,AS

Nom du mois

monthname('19.7.2005')
datename(m,'19.7.2005')
date_format('19.7.2005', '%M'), month('19.7.2005')

'Juillet'

AS,DB
MS,ASE
MY

Conversion en date/heure

timestamp('2002/1/1')
to_timestamp('2002/1/1')
convert(timestamp, '2002/1/1')

'2002-01-01-00.00.00.000000'

DB,AS
OR
MS,ASE

Date courante

current_date
current date
getdate()
curdate(), now()
today()

'18/07/2005'

SQL,OR,MY
DB,AS,ASA
MS,ASE
MY
IN

Heure courante

current time
current_time
getdate()
curtime()
CURRENT_TIMESTAMP

'18:00:34'

AS,DB
SQL,MY
MS,ASE
MY
MS

Heure et date courantes

now(), current timestamp
current_timestamp
getdate()
current_timestamp()
current()

'2005-07-18-18.29.30.303032'

AS,DB
OR,AS,MS
SQL,MS,ASE
MY
IN

Écart avec le GMT

current timezone
current_timestamp
datediff(hh, getutcdate(),getdate())

20000
… +02:00
2

AS
OR
MS,ASE

Microseconde d'une heure

microsecond('02:03:04.129')
datepart(ms,'02:03:04.129')
date_format('02:03:04.129', '%f')

129

AS,DB,MY
MS,ASE
MY

Extraction du trimestre d'une date

quarter('1.7.2005')
datepart(q, '1.7.2005')

3

AS,DB,MY
MS,ASE

Extraction d'une partie d'une date/heure

year(), month(), day(),
hour(), minutes(), second()
datepart(hh, '1.1.2005 05:04:03')
extract(hour from '1.1.2005 05:04:03')
date_format('1.1.2005 05:04:03', '%h')

…

AS,DB,MY
AS,DB,MY
MS,ASE
OR,MY
MY

Prochain 2e jour de la semaine

next_day('19.07.2005',2)

'26.07.2005'

OR

No de la semaine

week('19-7-2005')
datepart(ww, '19-7-2005')
date_format('19-7-2005', '%V')
to_number(to_char(to_date('19.7.2005'),'IW'))

30

AS,DB,MY
MS,ASE
MY
OR

Jour de la semaine

dayofweek('19.7.2005')
datepart(w, '19.7.2005')
date_format('19.7.2005', '%w')

3
2

AS,DB,MY
MS,ASE
MY

Jour de l'année

dayofyear('19.7.2005')
datediff(d,'1.1.2005','19.7.2005')+1
date_format('19.7.2005', '%j')

200

AS,DB,MY
MS,ASE
MY

V - Fonctions numériques

Action

Fonction

Résultat

Compatibilité

Conversion en décimal

decimal('45')
convert(dec(9,2), '45')
to_number('45')
convert('45', decimal)
cast('45' as decimal)

45

DB,AS
MS,ASE
OR
MY
MS,MY

Conversion en entier

integer('7.7')
cast(7.6 as integer)
cast(7.6 as integer)
cast(7.6 as signed/unsigned)

7
7
8
8

AS,DB
AS,DB,MS,ASE
OR
MY

Conversion en nombre à virgule flottante

float(1.0/3)
cast(1/0.3 as float)
convert(float, 1/0.3)

0.333333…1

AS
AS,DB,MS,SQL,OR
MS,ASE

La plus grande valeur

greatest(5,9…)

9

OR,MY

La plus petite valeur

least(5,9…)

5

OR,MY

Valeur absolue

abs(-3.3)

3.3

DB,AS, ASE, ASA,PG,MS,OR,MY

Conversion en degrés

degrees(1)

57.29

AS,MY

Conversion en digits (sans décimales)

digits(3.56)
convert(int, (str_replace(convert(varchar(30), 3.56),'.','')))
cast((replace(cast ( 3.56 as varchar(30)),'.','')) as int)
replace(3.56,'.','')

365

AS,DB
ASE
MS
OR

Conversion en digits sur 10 positions

digits(123)
RIGHT('0000000000'+convert(varchar(10),123),10)
substr('0000000000'||123 ),-10)

0000000356

AS,DB
MS,ASE
OR

Exponentiel

exp(2)

7.389

MS,OR,DB,AS,MY

Arrondi supérieur

ceiling(3.4)
ceil(3.4)

4

MS,ASE,DB,AS,MY
OR,MY

Trigonométrie

sin(.1), cos(.1), tan(.1)
asin(.1), acos(.1), atan(.1)
sinh(.1), cosh(.1), tanh(.1)
cot(.1)
atan2(.3,.2)

…

MS,ASE,OR,DB,AS,MY
MS,ASE,OR,DB,AS,MY
OR,DB,AS
MS,ASE,DB,AS,MY
OR,MY

Logarithme népérien

ln(1)
LOG2()

0

OR,DB,AS
MY

Logarithme en base 10

log(1)
log10(1)
log(1,10)

0

MS,DB,AS,MY
MS,ASE,DB,AS,MY
OR

Modulo

mod(50,7)
50 % 7

1

OR,DB,AS,MY
MS,ASE,MY

PI

pi()

3.1415…

OR,MS,ASE,DB,AS,MY

Élévation à la puissance

power(2,4)

16

OR,MS,ASE,DB,AS,MY

Valeur aléatoire

rand(1)

…

DB,MS,ASE,AS,MY

Arrondi

round(4.576,2)
round(4.574,2)

4.58
4.57

DB,MS,ASE,OR,AS,MY

Signe d'un nombre

sign(-7)

-1

DB,MS,OR,AS,ASE,MY

Racine carrée

sqrt(16)

4

DB,MS,OR,AS,ASE,MY

Troncature sans arrondi

trunc(3.599,2)
truncate(3.599,2)

3.59

OR,DB,AS
MS,ASE,DB,AS,MY

VI - Autres fonctions

Action

Fonction

Résultat

Compatibilité

n premières lignes d'un set

SELECT * FROM A WHERE RRN(A) < 10
SELECT * FROM A FETCH FIRST 10 ROWS ONLY
SELECT TOP 10 * FROM A
SELECT * FROM A WHERE ROWNUM < 10
SELECT * FROM A LIMIT 0,10

10 premières lignes

AS
DB
MS,ASE,ASA
OR
MY

Fonction conditionnelle

decode('M','M','H','F')
Value ('M','M','H','F')
case 'M' when 'M' then 'H' else 'F' end

'H'

OR
DB,AS
SQL,OR,MS,ASE,ASA,IN,DB,AS,MY

1re valeur non nulle

coalesce(Null, 0…)
isnull(NULL,0)
Nvl(NULL,0)
NULLIF(NULL,0)

0

SQL,AS,DB,OR,MS,ASE,ASA,MY
OR,AS,ASA,DB,MS,ASE
OR,IN
SQL,ASA,ASE,MS

Utilisateur courant

current_user
User
user_name()
user_sname()

'Utilisateur'

SQL,MS,DB,MY
OR
ASE
MS

Utilisateur de la session

session_user
suser_name()
suser_sname()
user()

'Login'

SQL,MS,DB,MY
ASE
MS
MY

Utilisateur système

System_user
Userenv

'Login système'

SQL,MS,DB
OR