Les expressions rationnelles dans SQL Server 2025

SQL Server 2025 introduit pour la première fois le support natif des expressions rationnelles directement dans T-SQL. Cette fonctionnalité attendue depuis plus de 20 ans élimine le recours aux assemblies CLR et simplifie de nombreux cas de recherche, comme la validation de contraintes check, l’extraction et la transformation de données complexes. L’intégration native permet d’utiliser les regex dans des requêtes complexes, contraintes, index filtrés, et en interopérabilité avec d’autres fonctionnalités dans SQL Server.

Les expressions rationnelles (regex) sont un outil fondamental pour la manipulation avancée des chaînes de caractères, permettant de définir des motifs complexes pour la recherche, la validation et la transformation de données textuelles.

Les expressions rationnelles avant SQL Server 2025

Avant SQL Server 2025, la gestion des expressions rationnelles dans SQL Server était très limitée. Les fonctions natives telles que LIKE, PATINDEX, ou STRING_SPLIT offrent des capacités basiques de recherche et de découpage de chaînes, mais elles sont loin de la puissance et de la flexibilité des regex modernes. Par exemple, LIKE ne supporte qu’une syntaxe simplifiée avec des caractères jokers (%, _), et PATINDEX permet seulement de trouver la position d’un motif simple dans une chaîne. Pour pallier ces limitations, les développeurs devaient souvent recourir à des solutions alternatives :

• fonctions personnalisées via SQL CLR : en développant des fonctions en .NET intégrées dans SQL Server via Common Language Runtime, il était possible d’utiliser des bibliothèques regex complètes, mais cela nécessitait des compétences en programmation avancée, une gestion des dépendances, et pouvait impacter les performances.
• Appels à des services externes : certaines solutions consistaient à appeler des services web ou des scripts externes pour effectuer les traitements regex, ce qui compliquait l’architecture et introduisait des latences.
• Traitements en dehors de la base : extraction des données, traitement via des langages comme Python, puis réinjection dans SQL Server, ce qui était lourd et peu efficace.

Fonctions regex dans SQL Server 2025

SQL Server 2025 propose cinq fonctions scalaires et deux fonctions tables (TVF, pour Table Valued Functions) pour manipuler les expressions rationnelles.

Les fonctions scalaires retournent une valeur par ligne : REGEXP_LIKE teste si une chaîne correspond à un pattern (booléen), REGEXP_COUNT compte les occurrences d’un motif, REGEXP_INSTR localise la position d’une correspondance, REGEXP_REPLACE effectue des remplacements, et REGEXP_SUBSTR extrait des sous-chaînes.

Les fonctions TVF génèrent des ensembles de lignes : REGEXP_MATCHES détaille toutes les correspondances avec leurs positions, tandis que REGEXP_SPLIT_TO_TABLE découpe une chaîne en utilisant un pattern comme délimiteur.

Ces fonctions s’appuient sur la bibliothèque RE2 de Google, fiable et performante. Elle garantit un temps d’exécution linéaire par rapport à la taille de l’entrée, évitant les attaques par déni de service regex (ReDoS) possibles avec d’autres moteurs. Cette architecture assure une exécution sécurisée même avec des patterns complexes.

Les plateformes supportées incluent SQL Server 2025 (17.x), Azure SQL Database, Azure SQL Managed Instance (avec politique de mise à jour “SQL Server 2025” ou “Always-up-to-date”), et SQL database in Microsoft Fabric.

REGEXP_LIKE, REGEXP_MATCHES et REGEXP_SPLIT_TO_TABLE nécessitent le niveau de compatibilité 170 :

-- Vérifier le niveau actuel
SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME();

-- Modifier si nécessaire
ALTER DATABASE [MaBase] SET COMPATIBILITY_LEVEL = 170;

Restrictions

• Les LOB varchar(max) et nvarchar(max) sont supportés avec une limite de 2 Mo pour REGEXP_LIKE, REGEXP_COUNT et REGEXP_INSTR uniquement. Les fonctions REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_MATCHES et REGEXP_SPLIT_TO_TABLE ne supportent pas les LOB.
• Trois restrictions limitent l’utilisation des regex dans certains contextes : les procédures stockées compilées nativement (In-Memory OLTP) ne peuvent pas exécuter de fonctions regex, les tables Memory-Optimized sont incompatibles, et les fonctions regex ne respectent pas les collations SQL pour les comparaisons linguistiques (comportement strictement basé sur Unicode).
• La longueur maximale d’un pattern est de 8 000 octets. Les répétitions avec quantificateurs explicites {n}, {n,m}, {n,} sont limitées à 1 000 répétitions. Les fonctions ne supportent pas le lookahead ((?=...)) ni le lookbehind ((?<=...)) dans la syntaxe RE2.

La fonction REGEXP_LIKE valide les correspondances

REGEXP_LIKE teste si une chaîne correspond à un pattern. Sa syntaxe complète est :

REGEXP_LIKE ( string_expression, pattern_expression [ , flags ] )

Le paramètre string_expression accepte les types char, nchar, varchar ou nvarchar, incluant les types LOB jusqu’à 2 Mo. Le pattern_expression définit le motif regex avec une limite de 8 000 octets. Le paramètre optionnel flags (maximum 30 caractères) contrôle le comportement de la correspondance (par exemple insensibilité à la casse). La fonction retourne un booléen true ou false, comme un LIKE classique, pour être utilisé dans un prédicat.

-- Changer le niveau de compatibilité : 170 (SQL Server 2025)
ALTER DATABASE PachadataTraining SET COMPATIBILITY_LEVEL = 170;
GO
USE PachadataTraining
GO

-- Validation d'emails
SELECT
    ContactId,
    Email,
    CASE
        WHEN REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,6}$')
        THEN 'Email valid'
        ELSE 'Email invalid'
    END AS ValidationEmail
FROM Contact.Contact
WHERE Email IS NOT NULL
ORDER BY ContactId;

-- Clause WHERE avec REGEXP_LIKE : filtrer les noms contenant des caractères non alphabétiques
SELECT
    ContactId,
    FirstName,
    LastName
FROM Contact.Contact
WHERE REGEXP_LIKE(LastName, '[^A-Za-zÀ-ÿ\s\-\'']');

L’utilisation dans une contrainte CHECK garantit l’intégrité des données à l’insertion :

ALTER TABLE Contact.Contact
ADD CONSTRAINT CK_Contact_Email_Valid
CHECK (REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'));

ALTER TABLE Contact.Contact
ADD CONSTRAINT CK_Contact_Telephone_Valid
CHECK (REGEXP_LIKE(Phone, '^\d{2}[\s.-]?\d{2}[\s.-]?\d{2}[\s.-]?\d{2}[\s.-]?\d{2}$'));

REGEXP_REPLACE pour transformer les chaînes

REGEXP_REPLACE offre des capacités de remplacement avancées avec support des groupes de capture. Sa syntaxe complète :

REGEXP_REPLACE ( string_expression, pattern_expression 
    [ , string_replacement [ , start [ , occurrence [ , flags ] ] ] ] )

Le paramètre string_replacement définit la chaîne de substitution (vide par défaut). Le paramètre start indique la position de départ (défaut: 1). Le paramètre occurrence spécifie quelle occurrence remplacer : 0 remplace toutes les occurrences, tandis qu’un entier positif cible la Nième occurrence uniquement.

Vous pouvez utiliser des back references (références arrière) : \1 à \9 référencent les groupes capturés, et & référence le pattern entier correspondant.

-- Formatage d'un numéro de téléphone avec groupes de capture
DECLARE @phone NVARCHAR(20) = '0612345678';
SELECT REGEXP_REPLACE(
    @phone,
    '(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})',
    '\1 \2 \3 \4 \5'
) AS FormattedPhone;
-- Résultat: 06 12 34 56 78

-- Masquage des 4 derniers chiffres d'une carte bancaire
SELECT REGEXP_REPLACE(NumeroCarte, '\d{4}$', '****') AS CarteMasquee
FROM Paiements;

-- Remplacement de la 2ème occurrence uniquement
SELECT REGEXP_REPLACE('A-B-C-D', '-', '/', 1, 2) AS Result;
-- Résultat: A-B/C-D

-- Suppression de tous les caractères non numériques
SELECT REGEXP_REPLACE(Telephone, '[^0-9]', '', 1, 0) AS TelephoneNettoye
FROM Contacts;

REGEXP_SUBSTR extrait des sous-chaînes

REGEXP_SUBSTR extrait la partie d’une chaîne correspondant au pattern spécifié. Elle fonctionne à n’importe quel niveau de compatibilité.

REGEXP_SUBSTR ( string_expression, pattern_expression 
    [ , start [ , occurrence [ , flags [ , group ] ] ] ] )

Le paramètre group permet d’extraire un groupe de capture spécifique : 0 retourne le match entier (défaut), tandis qu’un entier positif retourne le Nième groupe capturé.

-- Extraire le nom d'utilisateur et le domaine d'un email
SELECT
    ContactId,
    Email,
    REGEXP_SUBSTR(Email, '^[^@]+') AS UserName,
    REGEXP_SUBSTR(Email, '@(.+)$', 1, 1, '', 1) AS Domain
FROM Contact.Contact
WHERE Email IS NOT NULL;

-- Extraction du prénom et du nom séparément
DECLARE @Name varchar(50) = 'Isabelle D''Arco';

SELECT @Name,
    REGEXP_SUBSTR(@Name, '\w+', 1, 1) AS FirstName,
    REGEXP_SUBSTR(@Name, '[\w'']+', 1, 2) AS LastName;

-- Extraction d'un code postal français
DECLARE @address varchar(50) = '55 AVENUE MARECHAL FOCH 69006 LYON';
SELECT REGEXP_SUBSTR(@address, '\b\d{5}\b', 1, 1) AS CodePostal;

-- Premier mot commençant par une voyelle
SELECT
    REGEXP_SUBSTR(m.text, '\b[aeiouàâäéèêëïîôùûü]\w*', 1, 1, 'i') AS Word,
    m.text
FROM sys.messages m;

REGEXP_COUNT quantifie les occurrences

REGEXP_COUNT compte le nombre de correspondances d’un pattern dans une chaîne :

REGEXP_COUNT ( string_expression, pattern_expression [ , start [ , flags ] ] )

Cette fonction supporte les types LOB ([n]varchar(max)) jusqu’à 2 Mo et retourne un entier.

-- Compter les chiffres dans une chaîne
SELECT TOP 10
    MontantHT,
    REGEXP_COUNT(CAST(MontantHT as varchar(50)), '\d') AS NombreChiffres
    -- pas de conversion implicite ...
FROM Enrollment.Invoice;

-- Compter les mots
SELECT verse,
    REGEXP_COUNT(verse, '\b\w+\b') AS NbOfWords
FROM AI.Verses;

-- Compter les clés dans une chaîne JSON
SELECT TOP 10
    JsonInfo,
    REGEXP_COUNT(ClobInfo, '"[^"]+"\s*:') AS NombreCles
FROM Travel.Restaurant;

REGEXP_INSTR localise les correspondances

REGEXP_INSTR retourne la position d’une correspondance avec contrôle fin sur le résultat :

REGEXP_INSTR ( string_expression, pattern_expression 
    [ , start [ , occurrence [ , return_option [ , flags [ , group ] ] ] ] ] )

Le paramètre return_option détermine si la fonction retourne la position de début (0, défaut) ou de fin (1) de la correspondance.

-- Trouver la position du domaine dans l'email
SELECT
    ContactId,
    Email,
    REGEXP_INSTR(Email, '[^@][A-Za-z0-9.-]+\.[A-Za-z]{2,}$') AS PositionDomain,
    REGEXP_INSTR(Email, '@') AS PositionAt
FROM Contact.Contact
WHERE Email IS NOT NULL;

-- Position des espaces dans les noms complets
SELECT
    ContactId,
    CONCAT(FirstName, ' ', LastName) AS NomComplet,
    REGEXP_INSTR(CONCAT(FirstName, ' ', LastName), '\s') AS FirstSpace,
    REGEXP_INSTR(CONCAT(FirstName, ' ', LastName), '\s', 1, 2) AS SecondSpace
FROM Contact.Contact
WHERE FirstName IS NOT NULL AND LastName IS NOT NULL;

REGEXP_MATCHES (TVF) détaille toutes les correspondances

REGEXP_MATCHES retourne une table avec le détail de chaque correspondance :

REGEXP_MATCHES ( string_expression, pattern_expression [ , flags ] )

La table retournée contient les colonnes : match_id (bigint, séquence), start_position (int), end_position (int), match_value (même type que l’entrée), et substring_matches (json, détails des sous-groupes).

-- Extraire tous les hashtags d'un texte
SELECT * FROM REGEXP_MATCHES(
    'Découvrez #SQLServer2025 et les #Regex pour #DataProcessing',
    '#([A-Za-z0-9_]+)'
);
-- Retourne 3 lignes avec détails de chaque hashtag

-- Utilisation avec CROSS APPLY sur une table
SELECT E.EmployeID, M.match_id, M.match_value
FROM Employes E
CROSS APPLY REGEXP_MATCHES(E.Competences, '\w+') AS M;

REGEXP_SPLIT_TO_TABLE (TVF) découpe les chaînes

REGEXP_SPLIT_TO_TABLE divise une chaîne en lignes selon un pattern délimiteur :

REGEXP_SPLIT_TO_TABLE ( string_expression, pattern_expression [ , flags ] )

La table retournée contient value (sous-chaîne) et ordinal (position 1-based).

-- Découper une liste de compétences
SELECT * FROM REGEXP_SPLIT_TO_TABLE(
    'SQL, Python, Java, JavaScript',
    ',\s*'
);
-- Retourne: SQL(1), Python(2), Java(3), JavaScript(4)

-- Découper un texte en mots
SELECT value, ordinal 
FROM REGEXP_SPLIT_TO_TABLE('Le chat mange la souris', '\s+');

-- Utilisation avec une table
SELECT C.ClientID, S.value AS Tag, S.ordinal
FROM Clients C
CROSS APPLY REGEXP_SPLIT_TO_TABLE(C.Tags, ';\s*') AS S;

La syntaxe RE2

Métacaractères et caractères spéciaux : Le moteur RE2 supporte les métacaractères standards : . correspond à tout caractère sauf newline, ^ ancre au début, $ ancre à la fin, \ échappe les caractères spéciaux, | représente l’alternance (ou logique), et les crochets [] définissent les classes de caractères.

Classes de caractères Perl : Les raccourcis Perl simplifient les patterns courants : \d équivaut à [0-9] (chiffres), \D à [^0-9] (non-chiffres), \s aux espaces blancs (espace, tab, newline), \S aux non-espaces, \w aux caractères de mot [0-9A-Za-z_], et \W aux non-caractères de mot.

Classes POSIX : Les classes POSIX offrent une alternative lisible : [[:alnum:]] pour les alphanumériques, [[:alpha:]] pour les alphabétiques, [[:digit:]] pour les chiffres, [[:lower:]] et [[:upper:]] pour les minuscules/majuscules, [[:space:]] pour les espaces, [[:word:]] pour les caractères de mot, et [[:xdigit:]] pour les hexadécimaux.

Quantificateurs : Les quantificateurs contrôlent les répétitions : * signifie zéro ou plus (greedy), + un ou plus, ? zéro ou un, {n} exactement n occurrences, {n,m} entre n et m, {n,} n ou plus. Les versions non-greedy (lazy) ajoutent un ? : *?, +?, ??, {n,m}?.

Groupes et références : Les parenthèses créent des groupes de capture numérotés : (pattern). Les groupes non-capturants (?:pattern) optimisent le traitement. Les groupes nommés (?P<nom>pattern) améliorent la lisibilité. Les références arrière \1 à \9 permettent de réutiliser les captures.

Les parenthèses créent des groupes de capture numérotés : (pattern). Les groupes non-capturants (?:pattern) optimisent le traitement. Les groupes nommés (?P<nom>pattern) améliorent la lisibilité. Les références arrière \1 à \9 permettent de réutiliser les captures.

Ancres et limites de mots : Les ancres positionnent le match : ^ début de chaîne/ligne, $ fin de chaîne/ligne, \A début absolu de chaîne, \z fin absolue, \b limite de mot (word boundary), \B non-limite de mot.

Quatre flags contrôlent le comportement des correspondances

Les flags se combinent dans une chaîne : 'im' active l’insensibilité à la casse et le mode multi-ligne. En cas de flags contradictoires (exemple : 'ic'), le dernier spécifié prévaut.

-- Recherche insensible à la casse
SELECT * FROM Produits 
WHERE REGEXP_LIKE(NomProduit, 'ordinateur', 'i');

-- Mode multi-ligne pour traiter des textes sur plusieurs lignes
SELECT REGEXP_REPLACE(Contenu, '^\s+', '', 1, 0, 'm') AS ContenuNettoye
FROM Articles;

-- Combinaison de flags
SELECT REGEXP_COUNT(TexteMultiLigne, '^ligne', 1, 'im') AS NombreLignes
FROM Documents;

Exemples concrets d’utilisation

Validation d’adresses email

-- Pattern email complet avec validation TLD
DECLARE @PatternEmail NVARCHAR(200) = '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

-- Filtrer les emails valides
SELECT *
FROM Contact.Contact
WHERE REGEXP_LIKE(Email, @PatternEmail, 'i');

-- Rapport de qualité des données
SELECT
    COUNT(*) AS TotalEmails,
    SUM(CASE WHEN REGEXP_LIKE(Email, @PatternEmail, 'i') THEN 1 ELSE 0 END) AS EmailsValides,
    SUM(CASE WHEN NOT REGEXP_LIKE(Email, @PatternEmail, 'i') THEN 1 ELSE 0 END) AS EmailsInvalides
FROM Clients;

Validation de numéros de téléphone français

-- Téléphone fixe ou mobile français (10 chiffres commençant par 0)
DECLARE @PatternTelFR NVARCHAR(100) = '^0[1-9](\s?|-?|\.?)\d{2}(\s?|-?|\.?)\d{2}(\s?|-?|\.?)\d{2}(\s?|-?|\.?)\d{2}$';

-- Validation
SELECT Phone,
    CASE WHEN REGEXP_LIKE(Phone, @PatternTelFR) 
         THEN 'Valide' ELSE 'Invalide' END AS Statut
FROM Contact.Contact;

-- Normalisation (garder uniquement les chiffres)
UPDATE Contact.Contact
SET PhoneNormalise = REGEXP_REPLACE(Phone, '[^0-9]', '', 1, 0)
WHERE REGEXP_LIKE(Phone, '\d');

Validation de codes postaux et SIRET

-- Code postal français (5 chiffres, commence par 01-95 ou 97-98)
DECLARE @PatternCP NVARCHAR(50) = '^(0[1-9]|[1-8]\d|9[0-5]|97[1-6]|98[46-9])\d{3}$';

SELECT Adresse, CodePostal,
    CASE WHEN REGEXP_LIKE(CodePostal, @PatternCP) 
         THEN 'Valide' ELSE 'Invalide' END AS StatutCP
FROM Adresses;

-- SIRET (14 chiffres)
DECLARE @PatternSIRET NVARCHAR(50) = '^\d{14}$';
SELECT * FROM Entreprises
WHERE REGEXP_LIKE(REGEXP_REPLACE(SIRET, '[\s.-]', '', 1, 0), @PatternSIRET);

Nettoyage et transformation de données

-- Supprimer les espaces multiples
UPDATE Documents
SET Contenu = REGEXP_REPLACE(Contenu, '\s+', ' ', 1, 0)
WHERE REGEXP_LIKE(Contenu, '\s{2,}');

-- Standardiser les noms (première lettre majuscule)
-- Note: nécessite une combinaison avec UPPER/LOWER
SELECT Nom,
    UPPER(REGEXP_SUBSTR(Nom, '^\w')) +
    LOWER(REGEXP_SUBSTR(Nom, '(?<=^\w).+')) AS NomStandardise
FROM Clients;

-- Extraction de données structurées depuis du texte libre
SELECT TexteOriginal,
    REGEXP_SUBSTR(TexteOriginal, '\b[A-Z]{2}\d{3}[A-Z]{2}\b') AS PlaqueFR,
    REGEXP_SUBSTR(TexteOriginal, '\b\d{5}\b') AS CodePostal,
    REGEXP_SUBSTR(TexteOriginal, '\b0[67]\d{8}\b') AS TelMobile
FROM Documents;

Masquage de données sensibles

-- Masquage de numéros de carte bancaire
SELECT
    NumeroCarte,
    REGEXP_REPLACE(
        NumeroCarte,
        '(\d{4})(\d{4})(\d{4})(\d{4})',
        'XXXX-XXXX-XXXX-\4'
    ) AS CarteMasquee
FROM Paiements;

-- Masquage partiel des emails
SELECT Email,
    REGEXP_REPLACE(
        Email,
        '^(.{2})(.*)(@.+)$',
        '\1***\3'
    ) AS EmailMasque
FROM Contact.Contact;

Analyse des Performance

Les tests de performance révèlent des écarts significatifs entre les regex natives et les méthodes traditionnelles. Sur une base Stack Overflow de 9 millions de lignes, une requête avec REGEXP_LIKE consomme environ 60 secondes CPU contre 2,4 secondes pour une requête équivalente avec LIKE, soit un ratio de 25x. Des tests sur 1 million d’emails montrent que la validation T-SQL classique prend 4,3 secondes contre 42 secondes pour REGEXP_LIKE, un ratio de 10x.

Ces écarts s’expliquent par l’architecture : les fonctions regex appellent la bibliothèque externe RE2 pour chaque ligne (Compute Scalar en boucle), tandis que LIKE bénéficie d’une implémentation native optimisée du moteur SQL Server.

Comparaison LIKE versus PATINDEX

Pour les patterns simples, LIKE et PATINDEX offrent des performances quasi-identiques. PATINDEX peut être légèrement plus lent car il retourne une position (entier) plutôt qu’un simple booléen. Les deux utilisent les index de manière similaire et bénéficient de l’optimisation du moteur de requêtes.

Comparaison avec les solutions CLR

Avant SQL Server 2025, les expressions régulières nécessitaient des assemblies CLR (Common Language Runtime). Le regex natif présente des avantages majeurs : aucune installation ni configuration, disponibilité sur Azure SQL Database et Managed Instance, maintenance assurée par Microsoft, et intégration transparente à T-SQL.

Côté performance, les CLR appellent .NET System.Text.RegularExpressions avec un overhead par ligne. Pour des patterns très complexes avec multiples remplacements, le CLR peut parfois surpasser le T-SQL pur (40ms vs 210ms dans certains cas), mais le regex natif bénéficie désormais du parallélisme SQL Server, réduisant significativement l’elapsed time (jusqu’à 6x avec parallélisme activé).

Estimation de cardinalité et plans d’exécution

SQL Server utilise une estimation de cardinalité fixe de 30% pour les prédicats regex, entraînant parfois des plans d’exécution sous-optimaux. Deux nouveaux hints corrigent ce problème :

SELECT * FROM Clients
WHERE REGEXP_LIKE(Email, '@gmail\.com$')
OPTION (
    USE HINT('ASSUME_FIXED_MIN_SELECTIVITY_FOR_REGEXP'),
    USE HINT('ASSUME_FIXED_MAX_SELECTIVITY_FOR_REGEXP')
);

L’estimation de cardinalité des REGEXP_LIKE peut être corrigée par IQP (Intelligent Query Processing) avec le feedback d’estimation de cardinalité.

Les index ne sont pas utilisés efficacement avec les regex car le matching est basé sur les propriétés Unicode, indépendamment des collations SQL. Le plan d’exécution montre systématiquement des Index Scan plutôt que des Seek.

Stratégies d’optimisation pour réduire l’impact CPU

Pré-filtrer avec des prédicats rapides

La stratégie la plus efficace consiste à réduire le volume de données avant d’appliquer le regex :

-- Mauvais : regex sur toute la table
SELECT * FROM Clients
WHERE REGEXP_LIKE(Email, '^[a-z]+\d+@gmail\.com$', 'i');

-- Bon : pré-filtrage avec LIKE puis regex
SELECT * FROM Clients
WHERE Email LIKE '%@gmail.com'  -- Filtre rapide d'abord
AND REGEXP_LIKE(Email, '^[a-z]+\d+@gmail\.com$', 'i');  -- Regex sur subset

-- Encore mieux : utiliser plusieurs conditions LIKE
SELECT * FROM Clients
WHERE Email LIKE '%@gmail.com'
AND Email NOT LIKE '%[^a-z0-9@.]%'  -- Caractères autorisés
AND LEN(Email) BETWEEN 10 AND 50;

Persister les résultats pour les validations fréquentes

Pour les colonnes fréquemment validées, calculer et stocker le résultat évite les recalculs :

-- Ajouter une colonne calculée persistée
ALTER TABLE Clients ADD EmailValide AS (
    CAST(
        CASE WHEN REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') 
             THEN 1 ELSE 0 END 
        AS BIT
    )
) PERSISTED;

-- Index sur la colonne persistée
CREATE INDEX IX_Clients_EmailValide ON Clients(EmailValide);

Utiliser le parallélisme

Le regex natif bénéficie du parallélisme SQL Server. Éviter d’encapsuler dans des fonctions scalaires utilisateur qui forcent l’exécution en série :

-- Bon : regex directement dans la requête (parallélisable)
SELECT ClientID, Email
FROM Clients
WHERE REGEXP_LIKE(Email, pattern)
OPTION (MAXDOP 8);

-- Mauvais : encapsulé dans une UDF scalaire (force MAXDOP 1)
CREATE FUNCTION dbo.ValiderEmail(@email NVARCHAR(320))
RETURNS BIT AS BEGIN
    RETURN CASE WHEN REGEXP_LIKE(@email, pattern) THEN 1 ELSE 0 END;
END;

Bonnes pratiques pour une utilisation efficace

Quand utiliser les regex natives

Les regex sont appropriées pour la validation de données complexes (emails, téléphones, codes postaux), les contraintes CHECK sur colonnes, le nettoyage de données ponctuel ou ETL batch, le traitement de jeux de données déjà filtrés (quelques milliers de lignes), et les patterns impossibles avec LIKE (quantificateurs, alternances, groupes de capture).

Quand éviter les regex

Les alternatives traditionnelles restent préférables pour les requêtes OLTP haute fréquence, les clauses WHERE sur grandes tables sans pré-filtrage, le traitement temps réel (requêtes récurrentes chaque minute), les patterns simples réalisables avec LIKE ou PATINDEX, et les données LOB dépassant 2 Mo.

Règles de conception des patterns

• Ancrer les patterns avec ^ et $ quand possible pour éviter les recherches partielles coûteuses
• Utiliser les quantificateurs non-greedy (*?, +?) pour réduire le backtracking
• Éviter les patterns trop génériques comme .* sans ancres
• Préférer les classes de caractères explicites [0-9] plutôt que \d pour la lisibilité
• Tester avec STATISTICS IO, TIME avant déploiement en production

-- Activation des statistiques pour analyse
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM GrandeTable 
WHERE REGEXP_LIKE(Colonne, 'pattern');

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Migration depuis LIKE et PATINDEX vers les regex

Équivalences de patterns

Cas justifiant la migration

La migration vers les regex se justifie quand LIKE atteint ses limites : validation avec longueurs variables ({n,m}), alternances multiples (option1|option2|option3), extraction de sous-chaînes matchées, remplacements avec groupes de capture, ou comptage d’occurrences.

-- LIKE ne peut pas faire ça : valider 2 à 4 chiffres suivis d'une lettre
-- Avant (approximatif avec LIKE)
WHERE Colonne LIKE '[0-9][0-9][A-Z]' 
   OR Colonne LIKE '[0-9][0-9][0-9][A-Z]'
   OR Colonne LIKE '[0-9][0-9][0-9][0-9][A-Z]'

-- Après (précis avec regex)
WHERE REGEXP_LIKE(Colonne, '^\d{2,4}[A-Z]$')

RE2 : protection contre le Catastrophic Backtracking

Le catastrophic backtracking (ou retour sur trace catastrophique) est un problème de performance critique qui survient dans certains moteurs d’expressions rationnelles (comme ceux de .NET, Java, Python, ou PCRE). Il peut causer une consommation de CPU de 100% et bloquer une application ou un serveur pendant des heures, voire indéfiniment, pour une simple chaîne de caractères. C’est la cause principale des attaques par déni de service basées sur les regex (ReDoS).

La plupart des moteurs de regex traditionnels utilisent une approche NFA (Non-deterministic Finite Automaton) avec backtracking. Lorsqu’ils rencontrent un quantificateur (comme * ou +), ils tentent une correspondance et, si la suite échoue, ils “reviennent en arrière” (backtrack) pour essayer une autre permutation.

Le problème survient lorsqu’il y a ambiguïté et imbrication de quantificateurs.

Prenons l’expression classique :

$$(x+x+)+y$$

Et appliquons-la à la chaîne : xxxxxxxxxxxx (une suite de ‘x’ sans ‘y’ à la fin).

1. Le moteur essaie de faire correspondre le premier x+. Il prend tous les caractères.
2. Il échoue plus loin, donc il revient en arrière, relâche un caractère, et essaie de le faire correspondre avec le second x+.
3. À cause des parenthèses imbriquées (...)+, le nombre de combinaisons possibles pour diviser la chaîne entre les différents x+ augmente de façon exponentielle ($2^n$).
4. Pour une chaîne de seulement 30 caractères, le moteur peut devoir effectuer des millions, voire des milliards d’opérations avant de conclure que la chaîne ne correspond pas (car il manque le ‘y’).

En résumé : Le temps d’exécution explose non pas linéairement avec la taille du texte, mais exponentiellement.

Comment RE2 évite ce problème

RE2 (le moteur développé par Google) a été conçu spécifiquement pour résoudre ce problème.

Contrairement aux moteurs à backtracking récursifs, RE2 utilise la théorie des automates pour construire un DFA (Deterministic Finite Automaton).

• Principe : Il ne “devine” pas un chemin pour revenir en arrière ensuite. Il gère tous les états possibles simultanément à mesure qu’il avance dans la chaîne.
• Garantie : RE2 garantit une complexité temporelle linéaire O(n) par rapport à la taille de l’entrée. Le temps de traitement ne dépend pas de la complexité de l’expression régulière (sauf lors de la phase de compilation initiale), mais uniquement de la longueur du texte à analyser.

Pour obtenir cette sécurité, RE2 sacrifie certaines fonctionnalités avancées qui nécessitent du backtracking :

• Pas de Backreferences : Impossible d’écrire (a|b)\1 pour matcher “aa” ou “bb”.
• Pas de Lookarounds complexes : Les assertions avant/arrière ((?=...), (?<=...)) sont souvent limitées ou absentes.

Puisque le moteur sous-jacent est RE2, il est techniquement impossible de déclencher une boucle de backtracking exponentielle (ReDoS) dans SQL Server 2025 avec les fonctions regex natives. Une requête malveillante type (a+)+ sur une longue chaîne échouera ou réussira en temps linéaire, sans faire tomber le CPU du serveur.

[Besoin de services avec SQL Server ? Contactez-moi]