四有新人

CREATE FUNCTION [dbo].[Split]
(
-- Add the parameters for the function here
@splitString   VARCHAR(8000),
@separate   VARCHAR(10)
)
RETURNS @returnTable  TABLE(col_Value   VARCHAR(20))
AS
BEGIN

DECLARE   @thisSplitStr   VARCHAR(20)
DECLARE   @thisSepIndex   INT
DECLARE   @lastSepIndex   INT

SET   @lastSepIndex   =   0

IF   RIGHT(@splitString   ,len(@separate))   <>   @separate   SET   @splitString   =   @splitString   +   @separate
SET   @thisSepIndex   =   CharIndex(@separate,@splitString   ,@lastSepIndex)

while   @lastSepIndex   <=   @thisSepIndex
BEGIN
SET   @thisSplitStr   =   SUBSTRING(@splitString   ,@lastSepIndex,@thisSepIndex-@lastSepIndex)
SET   @lastSepIndex   =   @thisSepIndex   +   1
SET   @thisSepIndex   =   CharIndex(@separate,@splitString   ,@lastSepIndex)
INSERT   INTO   @returnTable   VALUES(@thisSplitStr)
END
RETURN

END

GO

DECLARE @INDEX INT
SELECT @INDEX = 1
DECLARE @charNumber INT
DECLARE @chartID INT

SELECT IDENTITY(INT,1,1) indexID, col_Value INTO #tmp FROM Split('12;32;43',';')
SELECT @charNumber = COUNT(*) FROM #tmp
while(@INDEX <= @charNumber)
BEGIN
SELECT @chartID = col_Value FROM #tmp WHERE @INDEX = indexID
SELECT @chartID
SELECT @INDEX=@INDEX + 1
END
DROP TABLE #tmp