décembre
2011
Un article de zinzineti
La norme SQL prévoit quelques fonctions pour le traitement des littéraux*. La fonction TRANSLATE fait partie des standards de la norme SQL. Cette fonction est implémentée nativement dans Oracle, dans DB2, dans PostgreSQL,… mais pas dans SQL SERVER 2012 ! MS n’arrête pas de faire évoluer son SGBD SQL SERVER en implémentant de nouvelles fonctionnalités afin de faciliter la tâche aux DBAs/Développeurs. Mais ce que je ne comprends pas c’est le fait que certaines fonctions SQL très pratiques ne sont toujours pas encore implémentées dans SQL SERVER 2012 ! Dans ce billet nous allons écrire pour SQL SERVER la fonction TRANSLATE en T-SQL et en CLR (C# .NET) puis montrer quelques applications pratiques de cette fonction.
La fonction TRANSLATE (chaine,caractere_a_remplacer,caractere_de_remplacement) remplace chaque caractère de la chaîne "chaine" présent dans "caractere_a_remplacer" par le caractère correspondant (de même position) dans "caractere_de_remplacement". C’est l’équivalent de la fonction tr sous Unix/Linux
=> TRANSLATE vs REPLACE
–>Sous ORACLE
SQL> SELECT TRANSLATE('Bonjour','Bjr','*$#') FROM DUAL ; -- Résultat *on$ou#
–>Sous MS SQL SERVER
SELECT REPLACE(REPLACE(REPLACE('Bonjour','B','*'),'j','$'),'r','#'); -- Résultat *on$ou#
Juste avec ce petit exemple on voit la limite de la fonction REPLACE pour ce qui concerne la lisibilité du code et ce n’est pas tout ! d’abord écrivons la fonction TRANSLATE pour MS SQL SERVER
=> Fonction TRANSLATE (T-SQL)
–> Fonction T-SQL
CREATE FUNCTION dbo.F_TRANSLATE (
@chaine NVARCHAR(1024),
@caractere_a_remplacer NVARCHAR(1024),
@caractere_de_remplacement NVARCHAR(1024)
)
RETURNS NVARCHAR(1024)
AS
BEGIN
DECLARE @i SMALLINT = 1
WHILE @i <= LEN(@caractere_a_remplacer)
BEGIN
SELECT @chaine = REPLACE(@chaine,SUBSTRING(@caractere_a_remplacer,@i,1),SUBSTRING(@caractere_de_remplacement,@i,1))
SET @i += 1
END
RETURN @chaine
END
GO
–> Test
SELECT dbo.F_TRANSLATE('Bonjour','Bjr ','*$#') -- Résultat *on$ou#
=> Fonction TRANSLATE (CLR)
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class CLR_TRANSLATE
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString F_CLR_TRANSLATE(String chaine, String caractere_a_remplacer, String caractere_de_remplacement)
{
String strInit = "";
for (int i = 0; i < caractere_a_remplacer.Length; i++)
{
if (i < caractere_de_remplacement.Length)
{
strInit = caractere_de_remplacement.Substring(i, 1);
}
else
{
strInit = "";
}
chaine = chaine.Replace(caractere_a_remplacer.Substring(i, 1), strInit);
}
return chaine;
}
};
–> Test
SELECT dbo.F_CLR_TRANSLATE('Bonjour','Bjr','*$#'); -- Résultat *on$ou#
=> Applications de la fonction SQL TRANSLATE
La fonction TRANSLATE est pratique pour :
–>plus de lisibilité du code comparé à l’utilisation de la fonction REPLACE
–>effectuer des remplacements de chaine de caractères dans les opérations ensembliste SELECT/UPDAPTE
–>écrire des algorithmes d’encodage et de décodage
Exemple
Si mon encodage consiste à traduire :
‘.;/!1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ’ en
‘ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890?!/;.’
alors ce message ‘/?/4 ?DE L 8?DD.2? D?/C?E WW’ peut être décodé de la manière suivante :
MSG_DECODE
----------------------------
CECI EST 1 MESSAGE SECRET !!
–>rendre volontairement illisible une colonne : par exemple rendre illisible les mot de passe ou une clé de série
Exemple
PWD
-------------------
****#****#****#****
–> ….
——————————————
HAPPY QUERY AND MERRY CHRISTMAS !
—————————————–
* Détail des fonctions de traitement des littéraux à la Page 117 du livre intitulé SQL de Frédéric BROUARD – Rudi BRUCHEZ -Christian SOUTOU 3è édition publié chez PEARSON
—————————————-
— Etienne ZINZINDOHOUE
—————————————-