不确定您的功能,但这是我的:
CREATE FUNCTION dbo.FN_PARSENAME(@chunk VARCHAR(4000), @delimiter CHAR(1), @index INT )
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE
@curIndex INT = 0,
@pos INT = 1,
@prevPos INT = 0,
@result VARCHAR(1000)
WHILE @pos > 0
BEGIN
SET @pos = CHARINDEX(@delimiter, @chunk, @prevPos);
IF(@pos > 0)
BEGIN -- Characters between position and prevIoUs position
SET @result = SUBSTRING(@chunk, @prevPos, @pos-@prevPos)
END
ELSE
BEGIN -- Last Delim
SET @result = SUBSTRING(@chunk, @prevPos, LEN(@chunk))
END
IF(@index = @curIndex)
BEGIN
RETURN @result
END
SET @prevPos = @pos + 1
SET @curIndex = @curIndex + 1;
END
RETURN '' -- Else Empty
END
您这样称呼它:SELECT Address_Line1 = dbo.fn_Parsename(Merged,'|', 0) FROM Table
其中合并的字段是定界的,“ |” 是定界符,因此您将其定为“ \”,0是您想要的字符串的哪一部分,0是第一个。
以您的示例为例:
SELECT category = dbo.fn_Parsename(product_url,'\', 1)
, sub-category = dbo.fn_Parsename(product_url,'\', 2)
, brand = dbo.fn_Parsename(product_url,'\', 3)
, model# = dbo.fn_Parsename(product_url,'\', 4)
FROM Table
或0-3视情况而定。