既然你使用sql2000,sql 2000有一新功能 user defined function
打開enterprise manager ,你會看到user defined functiond,add一新的
function,把下面的code加進去.然後在程式內使用下面語法去抓回來
str:='select TMP=dbo.piece(你要抓的欄位,':',你要抓第幾個) from .. where....'
這語法應該沒錯,只是單引號問題請自己注意了.
CREATE FUNCTION piece (@Value VARCHAR(8000),@Delimiter
VARCHAR(1000),@PieceNumber INT)
RETURNS VARCHAR(8000) AS
begin
-- Declare
DECLARE @pos1 INT -- Start position of PieceNumber
DECLARE @pos2 INT -- End position of PieceNumber
DECLARE @int1 INT -- Loop variable
DECLARE @str1 VARCHAR(8000) -- PieceNumber found
DECLARE @str2 VARCHAR(8000) -- Value with extra delimiter added
-- Set Variables
SET @str2 = @Value + @Delimiter
SET @pos1 = 0
-- Find Piece
IF @PieceNumber = 1
begin
SET @pos2 = CHARINDEX(@Delimiter,@str2,1)
SET @str1 = CASE WHEN @pos2 > 1 then
SUBSTRING(@str2,1,@pos2-1) else
NULL
END
END
else
begin
SET @int1 = 1
WHILE @int1 < @PieceNumber
begin
SET @pos1 = CHARINDEX(@Delimiter,@str2,@pos1+1)
IF @pos1 = 0 GOTO EndPoint
SET @int1 = @int1+1
END
SET @pos1 = @pos1+LEN(@Delimiter)
SET @pos2 = CHARINDEX(@Delimiter,@str2,@pos1)
SET @str1 = CASE WHEN @pos2 > @pos1 then
SUBSTRING(@str2,@pos1,@pos2-@pos1) else
NULL END
END
EndPoint:
-- Return result
IF @str1 = '' SET @str1 = NULL
RETURN @str1
END