SQL: dbo.parseString() - varchar() to table variable

1 svar · 5 896 visningar · startad av toha 9 feb. 2005

tohaMedlem sedan dec. 1999707 inlägg

Frågan9 feb. 2005 00:08#1

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

ALTER     FUNCTION parseString
(
	@sIds	AS VARCHAR(8000)
)
RETURNS @Ret TABLE(IDs INT)
AS
BEGIN

	DECLARE @FirstPos	INT
	DECLARE @FirstPos1	INT
	DECLARE @SecondPos	INT
	
	IF ( datalength( @sIds ) > 0 )
	BEGIN
		SET @FirstPos = 1
		
		SELECT @SecondPos = charindex(',', @sIds, @FirstPos)
		
		WHILE ( @SecondPos > 0 )
		BEGIN
			INSERT INTO @Ret
			VALUES( cast( substring( @sIds, @FirstPos, @SecondPos - @FirstPos ) AS INT) )
		
			SET @FirstPos = @SecondPos + 1
			SET @SecondPos = charindex( ',', @sIds, @FirstPos )
		END

		INSERT INTO @Ret
		VALUES( cast( right( @sIds, datalength( @sIds ) - @FirstPos + 1 ) AS INT ) )
	END

	RETURN
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

TravoniMedlem sedan okt. 20041 556 inlägg

12 sep. 2007 19:02#2

Trixade till denna så att den skulle passa mySql. Därmot så kan inte funktioner i mysql levereras i table format så man får anropa proceduren "explicit".

PROCEDURE `parseString`(sIds varchar(1000))
BEGIN 
DECLARE FirstPos INT;
DECLARE FirstPos1 INT;
DECLARE SecondPos INT; 
SET FirstPos=1;
	IF (LENGTH(sIds) > 0) THEN
        DROP TABLE IF EXISTS TempTable;
        CREATE TEMPORARY TABLE TempTable(id int) ENGINE=MEMORY;
		SET SecondPos = LOCATE(',',sIds,FirstPos);
		WHILE (SecondPos>0) DO
			INSERT INTO TempTable
			VALUES(cast(substring(sIds, FirstPos, (SecondPos - FirstPos)) as unsigned INT));
			set FirstPos=SecondPos+1;
			SET SecondPos=LOCATE(',',sIds,FirstPos);
		END WHILE;
		INSERT INTO TempTable
		VALUES(cast(right(sIds, LENGTH(sIds) - FirstPos + 1 ) as unsigned INT));
	END IF;
END;

CALL parseString('1,3,5,6,7,89,10,11,17,20')

SELECT
 User.*
FROM
 User
 INNER JOIN
 TempTable as tmp
 ON
 tmp.id = User.id