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 30 31 32 33 34 35 36 37 38 39 40 41 42
| CREATE FUNCTION fctSplitToVarChar(
@DelimitedText VARCHAR(MAX), -- liste CSV
@QuoteChar CHAR(1) -- caractère de délimitation
) RETURNS @Items TABLE (Item VARCHAR(MAX))
BEGIN
DECLARE @Item VARCHAR(15)
WHILE CHARINDEX(@QuoteChar, @DelimitedText, 0) <> 0 BEGIN
SELECT @Item=SUBSTRING(@DelimitedText,1,CHARINDEX(@QuoteChar,@DelimitedText, 0)-1),
@DelimitedText=SUBSTRING(@DelimitedText,CHARINDEX(@QuoteChar,@DelimitedText, 0) + LEN(@QuoteChar), LEN(@DelimitedText))
IF LEN(RTRIM(@Item)) > 0
INSERT INTO @Items SELECT @Item
END
-- Dernier item de la liste
IF LEN(RTRIM(@DelimitedText)) > 0
INSERT INTO @Items SELECT @DelimitedText
RETURN
END
GO
/**********************/
CREATE FUNCTION dbo.fn_MVParam
(@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (Param nvarchar(4000))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(10)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(Param) VALUES(Cast(@Piece AS INT))
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END |
Partager