SQL 把(n)varchar类型转换为多列或多个value的集合
CREATE FUNCTION dbo.UFN_SEPARATES_COLUMNS(@TEXT VARCHAR(8000),
@COLUMN TINYINT,
@SEPARATOR CHAR(1)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @POS_START INT= 1;
DECLARE @POS_END INT= CHARINDEX(@SEPARATOR, @TEXT, @POS_START);
WHILE(@COLUMN > 1
AND @POS_END > 0)
BEGIN
SET @POS_START = @POS_END + 1;
SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START);
SET @COLUMN = @COLUMN - 1;
END;
IF @COLUMN > 1
SET @POS_START = LEN(@TEXT) + 1;
IF @POS_END = 0
SET @POS_END = LEN(@TEXT) + 1;
RETURN SUBSTRING(@TEXT, @POS_START, @POS_END-@POS_START);
END;
GO
页:
[1]