comment parser un string en sql

Version imprimable

10/06/2009, 19h29
jmclej

comment parser un string en sql
Bonjour,

Je suis sous SQL Server 2005.
J'ai la valeur "123$456$789" en entrée de ma procédure stockée dont je voudrais mettre les entiers, séparés par le caractère '$', dans une table temporaire
Code:

1 2 3 create table #listIds ( id int not null )
Quels sont à votre avis les meilleurs fonctions TSQL ou SQL Server afin de faire mon parsing à l'intérieur de ma boucle while s'il vous plaît?
D'avance merci,
Julien

Je vais répondre sur ce sujet-ci.

Vous pouvez vous en sortir avec une CTE (Edit, j'ai repris la CTE proposée ci-dessous par Elsuket, j'ai rajouté un tri) :

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
DECLARE @separateur varchar(1);
DECLARE @chaine varchar(50);
 
SELECT @separateur = '$';
SELECT @chaine = '123$456$789$89764$343531463545343543$00$7';
 
WITH BCL(Deb, Fin, Ord) AS 
( 
SELECT 1, CHARINDEX(@separateur, @chaine + @separateur), 1
UNION ALL 
SELECT Fin+1, CHARINDEX(@separateur, @chaine + @separateur, Fin + 1), Ord+1
FROM BCL 
WHERE CHARINDEX(@separateur, @chaine + @separateur, Fin + 1 ) > 0 
)
SELECT SUBSTRING(@chaine, Deb ,Fin-Deb)
FROM BCL
ORDER BY Ord ASC;
 
ENTIERS
------------------
123
456
789
89764
343531463545343543
00
7

La proposition initiale plus complexe :

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
DECLARE @INPUT VARCHAR(50);
SELECT @INPUT = '123$456$789$89764$343531463545343543$00$7';
 
 
WITH BCL AS
(
SELECT
    CASE CHARINDEX('$',@INPUT)
      WHEN 0 THEN @INPUT 
      ELSE SUBSTRING(@INPUT,1,CHARINDEX('$',@INPUT,1)-1)  
    END AS SPT,
   SUBSTRING(@INPUT,CHARINDEX('$',@INPUT,2)+1,LEN(@INPUT)) AS RST
UNION ALL
SELECT
    CASE CHARINDEX('$',BCL.RST)
      WHEN 0 THEN BCL.RST 
      ELSE SUBSTRING(BCL.RST,1,CHARINDEX('$',BCL.RST,1)-1)  
    END,
    SUBSTRING(BCL.RST,CHARINDEX('$',BCL.RST,2)+1,LEN(BCL.RST))
FROM BCL
WHERE
    BCL.RST <> BCL.SPT
)
SELECT CAST(SPT AS NUMERIC(18)) AS ENTIERS
FROM BCL;

Bonjour,

Un autre exemple avec une fonction table :

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
29
ALTER FUNCTION Fn_Extrait_Nombre
	(
		@chaine VARCHAR(1024),
		@separateur CHAR(1)
	)
	RETURNS @TbListIds TABLE
	(
		Id BIGINT NOT NULL
	)
AS
BEGIN
	IF CHARINDEX(@separateur, REVERSE(@chaine)) <> 1
	BEGIN
		SET @chaine = @chaine + @separateur
	END
 
	DECLARE @res BIGINT
 
	WHILE LEN(@chaine) > 0
	BEGIN
		SELECT @res = CAST(SUBSTRING(@chaine, 1, CHARINDEX(@separateur, @chaine) - 1) AS BIGINT)
 
		INSERT INTO @TbListIds (Id) VALUES (@res)
 
		SELECT @chaine = STUFF(@chaine, 1, LEN(@res) + 1, '')
	END
 
	RETURN
END

Vous pouvez ensuite l'appeler comme suit :

Code:

1
2
SELECT Id
FROM dbo.Fn_Extrait_Nombre('123$456$789$89764$343531463545343543$00$7', '$')

Le désavantage de celle-ci est qu'elle utilise une variable table ...

Une autre exemple avec une CTE :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE FUNCTION Fn_Extrait_Nombre
	(
		@chaine VARCHAR(1024),
		@separateur CHAR(1)
	)
RETURNS TABLE
AS
RETURN
(
	WITH CTE (Deb, Fin) AS 
		( 
				SELECT 1 Deb, CHARINDEX(@separateur, @chaine + @separateur) Fin 
			UNION ALL 
			  SELECT Fin + 1, CHARINDEX(@separateur, @chaine + @separateur, Fin + 1) 
			  FROM CTE 
			  WHERE CHARINDEX(@separateur, @chaine + @separateur, Fin + 1 ) > 0 
		)
	SELECT SUBSTRING(@chaine, Deb , Fin - Deb) AS Id
	FROM CTE
)

L'utilisation est la même ;)

Dès lors vous n'avez plus besoin de placer cela dans une table temporaire, en réaliser vos jointures sur la fonction ou sur la CTE que vous propose Waldar ;)

@++ ;)

11/06/2009, 01h02
Waldar

Je préfère la CTE de votre solution, elle est plus courte pour le même résultat.
11/06/2009, 08h30
elsuket

Mais est-elle aussi performante ? :D
A jmclej de nous le dire !

@++ ;)
11/06/2009, 10h31
jmclej

Bonjour,
Déjà merci pour vos réponses.
Ensuite je tiens à dire que je ne suis pas nouveau en SQL mais nouveau en MS SQL Server (je viens du monde sybase) et donc... je ne sais malheureusement pas ce qu'est une CTE, pouvez-vous m'éclairer sur ce que c'est, est-ce une fonction, comment cela se déclare-t-il. Qu'est-ce que la syntaxe 'WITH BCL(Deb, Fin, Ord) AS'? Est-ce que c'est ça une CTE? Est-ce une fonction?... quelsques questions donc avant que je puisse utiliser vos solutions je crains.
Et sinon, est-ce que vous auriez une méthode purement SQL, sans utilisations de notions liées à MS SQL Server?
Encore merci,
Julien
11/06/2009, 10h55
jmclej

ok, je viens de comprendre ce qu'était une CTE, maintenant je vais voir comment l'utiliser. Est-ce que je peux tout faire à l'intérieur d'une seule procédure stockée ou bien est-ce qu'il faut faire mes déclarations en dehors? Je vais essayer. Malgré tout, je suis toujours intéressé par une solution purement SQL standard.
Merci merci,
Julien
11/06/2009, 10h58
SQLpro

Les CTE font partie du langage SQL depuis la norme SQL:1999 datant de 1999... Voir l'article que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/s...te-recursives/
A de rares exception près (MySQL par exemple) elles sont implémentées dans tous les SGBDR

A +

Merci pour votre aide, vous m'avez bien mis sur la voie.
J'ai réussi à coder une méthode plus 'T-SQL compliant', ce qui je pense doit être le plus efficace au niveau rapidité, non?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
create table #listIds (
	id int not null
)
declare @listId varchar, @val varchar(max), @char char(1)
select @listId = '4582$6585$4258$96$'
select @val = ''
WHILE (len(@listId) > 0)
begin
	select @char = substring(@listId, 0, 1)
	if (@char != '$')
	begin
		select @val = val + @char
		select @listId = substring(@listId, 1, len(@listId) - 1)
	end
	else
	begin
		if (len(@val) > 0)
			insert into #listIds values (convert(int, @val))
		select @val = ''
		select @listId = substring(@listId, 1, len(@listId) - 1)
	end
end

11/06/2009, 11h29
jmclej

Autant pour moi en effet... :-)

Citation:

Envoyé par SQLpro

Les CTE font partie du langage SQL depuis la norme SQL:1999 datant de 1999... Voir l'article que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/s...te-recursives/
A de rares exception près (MySQL par exemple) elles sont implémentées dans tous les SGBDR

A +
11/06/2009, 16h23
elsuket

La seule façon de le savoir, c'est de tester sur une machine de développement.
Je suppose que SyBase et SQL Server étant proches, vous pouvez utiliser SET STATISTICS TIME ON avant d'exécuter votre requête pour vous afficher les temps de traitement ...

Pour quelques exemples de CTE, c'est par ici

@++ ;)