低版本MSSQL的split_part

原创已于 2023-02-27 14:07:09 修改 · 285 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

于 2023-02-24 17:23:26 首次发布

数据库专栏收录该内容

2 篇文章

订阅专栏

该文章提供了一个名为easSplit_part的SQL函数，用于在不支持string_split函数的SQLServer2012及更低版本中实现字符串分割的功能。函数接受三个参数：要分割的字符串、分隔符和索引位置，返回指定索引位置的子字符串。

低版本客户端没有string_split

末尾不太对

-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Kevin Hanovich Kitanski
-- Create date: 2023-02-24
-- Description:	as string_split not available in sql13(2012)
-- ref www.bbsmax.com/A/ke5jexw9Jr
-- =============================================
alter FUNCTION dbo.easSplit_part
(
    @String nVARCHAR(MAX),
    @Delimiter nVARCHAR(MAX),
    @pnIndex int
)
RETURNS nvarchar(max)
AS
BEGIN
	if @String is null 
      return ''
	IF LEN(@String) < 1 OR LEN(ISNULL(@String,'')) = 0
        RETURN ''
    DECLARE @vnSubLeft int=1
	declare @vnStrLen int=LEN(ISNULL(@String,''))
	declare @vnSubRight int =0
	declare @vnFoundCnt int=0
    WHILE (@vnSubRight<@vnStrLen)
    BEGIN
        set @vnSubRight = CHARINDEX(@Delimiter,substring(@String,@vnSubLeft,@vnStrLen))        
		IF @vnSubRight=0 -- not found
			if (@vnFoundCnt =@pnIndex-1)
				return substring(@String,@vnSubLeft,@vnStrLen);
			else
				return ''
        set @vnFoundCnt = @vnFoundCnt+1		
		if (@vnFoundCnt =@pnIndex)
			return substring(@String,@vnSubLeft,@vnSubRight-1)
         -- return substring(@String,@vnSubLeft,@vnSubRight-1)+str(@vnSubLeft)+' '+str(@vnSubRight)
		set @vnsubLeft=@vnsubRight+@vnsubLeft+len(@delimiter)-1
    END
    RETURN ''
END