Une fonction de dépouillement d’une chaîne de valeurs séparées par des virgules

Publié le par

Je vous propose ici une fonction en pur T-SQL qui permet de retourner les valeurs que l’on trouve entre les virgules d’une chaîne en contenant un plus ou moins grand nombre.

Celle-ci repose sur une table de nombres, ce qui permet d’éviter de boucler par un WHILE, qui s’avère être contre-performant, SQL n’étant pas un langage itératif.

Il existe pour réaliser le même but plusieurs alternatives, la première et la plus évidente étant de créer une fonction de type table qui exécute une boucle par un WHILE en parcourant la chaîne à la recherche des valeurs entre les virgules.

Cela fonctionne très bien pour un faible nombre de valeurs, mais lorsque celui-ci grossit, et les applications, web tout particulièrement, qui sont à l’origine de ce « grossissement », le permettent facilement.

Étant donné que SQL n’est pas non plus un langage très performant sur les opérations de chaîne de caractère, on peut tout à fait se tourner vers une fonction d’assembly CLR.
L’avantage d’une telle fonction est qu’elle s’exécute très rapidement.

Malheureusement en ouvrant une base de données à l’utilisation CLR, on ouvre aussi une porte aux problèmes de sécurité, et peut-être de performance de SQL Server, qui doit instancier la CLR …

Je devais donc écrire une fonction en pur T-SQL qui fasse la même chose, avec une vitesse comparable.
Mes tests ont révélé que la fonction T-SQL est plus rapide qu’une fonction CLR, peut-être parce qu’un module CLR ne peut être exécuté que par un seul thread sous SQL Server …

Voici donc la table de nombres, très simple :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE dbo.nombre
(
  nombre int NOT NULL CONSTRAINT PK_Nombres PRIMARY KEY CLUSTERED
)
GO
 
SET NOCOUNT ON
GO
 
DECLARE  @max int = 300000
  , @i int = 1
 
WHILE @i <= @max
BEGIN
  INSERT dbo.nombre SELECT @i
  SET @i += 1
END
GO

La variable @max représente en fait le nombre de caractère maximal que la fonction va prendre en entrée
J’ai mis 300.000 car je suis sûr avec un tel nombre de couvrir tous les cas qui se présentent en production.
Mais rien ne nous empêche de mettre plus … ou moins :)

Et voici la fonction :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-------------------------------
-- Nicolas Souquet - 19/12/2011
-------------------------------
CREATE FUNCTION dbo.SplitStringToInt
  (
    @string AS varchar(max)
    , @sep AS char(1)
  )
RETURNS TABLE
AS
  RETURN
    SELECT  CAST(SUBSTRING(@string, nombre, CHARINDEX(@sep, @string + @sep, nombre) - nombre) AS int) AS element
    FROM  dbo.nombre
    WHERE  nombre <= LEN(@string) + 1
    AND  SUBSTRING(@sep + @string, nombre, 1) = @sep
GO

Bon dépouillement !

ElSüket :)

3 réflexions au sujet de « Une fonction de dépouillement d’une chaîne de valeurs séparées par des virgules »

  1. ok alors si je comprend bien je dois créer la table nombre avec mon estimation du plus d’enregistrements que l’on aura dans une liste, mais ce n’est pas grave si j’en met moins.
    Puis je doit créer une seule fonction « SplitStringToInt » et cette fonction je pourrais l’utiliser pour gérer des liste de int ce qui n’est pas possible nativement es-ce bien ça?

  2. Désolé pour la boulette, c’est une fonction que j’ai écrite en Anglais que j’ai mal traduite …
    Voilà qui est corrigé. La vitesse de la fonction dépend de l’existence de la table, qui ne doit être créée qu’une seule fois.

    Merci de l’avoir relevé ;)

  3. La table ne sert pour pour l’exemple (dbo.nombre == dbo.Numbers), dans ce cas la fonction SplitStringToInt sera à ré implémenter pour chaque utilisations

    ou

    La table fait partie intégrante de la fonction et on doit donc l’ajouter, voir la remplir avec le plus possible d’enregistrements et on est pas obligé de ré implémenter cette fonction pour chaques types d’utilisation?

Laisser un commentaire