Meddelande

Minska
No announcement yet.

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

Minska
X
  • Filter
  • Klockan
  • Show
Clear All
new posts

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

    [kod]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 = charinde',', @sIds, @FirstPos)

    WHILE ( @SecondPos > 0 )
    BEGIN
    INSERT INTO @Ret
    VALUES( cast( substring( @sIds, @FirstPos, @SecondPos - @FirstPos ) AS INT) )

    SET @FirstPos = @SecondPos + 1
    SET @SecondPos = charinde ',', @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
    [/kod]
    // Torbjörn
    In the gutter, watching the stars.

  • #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".
    Kod:
    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;
    Kod:
    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
    Qui rogat, non errat

    Kommentera

    Working...
    X