Utilisation d'expressions régulières dans une procédure stockée.

[LestoK]

Bonjour à tous,

je dois trouver dans une table "clients" l'ensemble des correspondances existants à partir de données saisies sur un écran (n° tel, code_postal, etc).

Comme les données qui alimentent cette table viennent de plusieurs applications (+/- récente) les règles de saisies ne sont hélas pas toutes les mêmes et je dois mettre suffisamment d'intelligence dans ma recherche de correspondances pour satisfaire la majorité des cas sans plomber les perfs.

Ma table contient près de 2 millions d'enregistrements (ce qui n'aide pas).

Exemple : Trouver les correspondances à partir d'un numéro de téléphone saisi. En saisissant l'un des numéros suivants, remonter les correspondances des clients avec un numéro dans la liste suivante.

0123456789
01.23.45.67.89
01/23/45/67/89
01 23 45 67 89
01-23-45-67-89
0033123456789

Si possible également : +33(0)123456789

J'ai essayer avec la fonction scalaire suivante en utilisant replace en enlevant les caractères suivants :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATEFUNCTION [dbo].[f_formater_telephone]
(	
	-- Add the parameters for the function here
	@telephone varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
 
	-- Add the SELECT statement with parameter references here
 
	SET @telephone=REPLACE(@telephone,' ','')
	SET @telephone=REPLACE(@telephone,'-','')
	SET @telephone=REPLACE(@telephone,'/','')
	SET @telephone=REPLACE(@telephone,'.','')
	SET @telephone=REPLACE(@telephone,'\','')
	SET @telephone=replace(@telephone,',','')
	SET @telephone=replace(@telephone,'_','')
	SET @telephone=replace(@telephone,':','')
	SET @telephone=replace(@telephone,'''','')
	return ltrim(rtrim(@telephone))
END

J'aurai également aimé enlever tous les caractères non numériques. Lettres, etc...

Est il possible d'utiliser les expressions régulières en SQL uniquement ?
Les perfs avec cette méthode sont... catastrophiques.

Une petite aide ? Une orientation ?
Je suis preneur de tout.

Merci à vous.

[mikedavem]

Bonsoir,

2 possibilités :

- Les objets OLE Automation
- Les fonctions SQL CLR

++

[iberserk]

Comment faites vous votre requêtes?

Le principe est de formater votre paramètre de recherche avant de lancer celle-ci?

[zinzineti]

Tu peux lire ceci

[aieeeuuuuu]

Bonjour

Envoyé par LestoK

dois mettre suffisamment d'intelligence dans ma recherche de correspondances pour satisfaire la majorité des cas sans plomber les perfs.

Ma table contient près de 2 millions d'enregistrements (ce qui n'aide pas).

Je pense qu'il va falloir commencer par nettoyer les données. Sans cela, vous serez dans tous les cas obligé de scanner la totalité de la table soit les deux millions de lignes pour voir si l'une d'entre elle correspond...

Idéalement, il faudrait ajouter des contraintes sur ce type de colonne (numéro de téléphone, ...) afin d'en unifier le format.
Si cela engendre trop de modifications coté applications, vous pouvez peut être prévoir une fonction (éventuellement CLR) pour reformater les numéros saisis... à placer dans des triggers INSERT/UPDATE. Si vous voulez néanmoins conserver les numéros tels qu'ils ont été saisis, vous pouvez aussi ajouter une colonne calculée persistante que vous pouvez indexer.

[elsuket]

Bonjour,

A partir de la fonction que j'ai donné ici, on peut écrire :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE FUNCTION [dbo].[getPhoneNumber] (@string varchar(20))
	RETURNS varchar(20)
	WITH SCHEMABINDING 
AS 
BEGIN 
	DECLARE @result VARCHAR(1024) 
		, @i int
		, @lgr int
		, @char char(1) 
 
	SELECT	@i = 1
		, @lgr = LEN(@string)
		, @result = '' 
 
	WHILE @i <= @Lgr 
	BEGIN 
		SELECT @char = SUBSTRING(@string, @i, 1) 
 
		IF ASCII(@char) BETWEEN 48 AND 57 
		BEGIN 
			SET @result = @result + @char 
		END 
 
		SET @i = @i + 1 
	END
 
	RETURN @result
END

En changeant encore un peu le code, on doit pouvoir obtenir ce que vous souhaitez.
J'espère que vous n'êtes pas dans un cadre international, parce que sinon c'est plus compliqué : il vous faudra stocker les expressions des standards internationaux dans une petite table, et faire la comparaison.

Normalement néanmoins, ce genre de corrections devrait être fait et vérifié au niveau de l'application

Il suffit ensuite de s'en servir pour faire une colonne calculée :

Code :

1
2
ALTER TABLE dbo.clients
ADD correct_phone_number AS (dbo.getPhoneNumber(phone_number)) PERSISTED

On peut ensuite indexer :

Code :

1
2
CREATE INDEX IXNC_clients_correct_phone_number
ON dbo.clients(correct_phone_number)

@++

[SQLpro]

C'est assez marrant car c'est un exemple que je donne dans mon cours d'optimisation à Orsys... En gros avec un REGEX on est à plus d'un millions de fois moins rapide sur un ensemble de ligne pas si délirant, par rapport à la colonne calculée persistante associée à une fonction de nettoyage.

A +

[elsuket]

En gros avec un REGEX on est à plus d'un millions de fois moins rapide sur un ensemble de ligne pas si délirant, par rapport à la colonne calculée persistante associée à une fonction de nettoyage.

Un million ça me semble quand même assez gros !

Mettons qu'on ait quelque chose de plus compliqué à faire : on implémente une fonction CLR, dont on se sert pour spécifier une colonne calculée que l'on indexe (pas obligé de la persister après tout !).

Qu'en penses-tu ?

@++

[iberserk]

C'est assez marrant car c'est un exemple que je donne dans mon cours d'optimisation à Orsys... En gros avec un REGEX on est à plus d'un millions de fois moins rapide sur un ensemble de ligne pas si délirant, par rapport à la colonne calculée persistante associée à une fonction de nettoyage.

Personnellement cela ne m'étonnes pas vraiment, même si un million peut paraître beaucoup.

J'ai récemment refait un lot d'import de fichier plat en passant de 15 heures à... 20 secondes-> passage d'une approche full code client ADO.NET (vous auriez appriécié Frédéric ) en simple requète ensembliste.

[iberserk]

Mettons qu'on ait quelque chose de plus compliqué à faire : on implémente une fonction CLR, dont on se sert pour spécifier une colonne calculée que l'on indexe (pas obligé de la persister après tout !).

Sauf que tu ne peux pas indexer une colonne calculée issue d'une fonction CLR sans la persister