use WebShowDBAlter
go
---1---
declare @str nvarchar(50)
select @str=DodgeRaterID from Student where ID=‘03‘
select dbo.Get_StrArrayStrOfIndex(@str,‘,‘,3)

---2----
select dbo.Get_StrArrayStrOfIndex(DodgeRaterID,‘,‘,4) from Student

--3--
select * from dbo.[fn_SplitStr](‘1,2,56,56‘,‘,‘)

--4--
select * from dbo.[fn_SplitStr]((select DodgeRaterID from Student where ID=‘06‘),‘,‘)

--5----
declare @str varchar(50)
set @str=‘11,22,3,4,5‘
declare @next int 
set @next=1
while @next<=dbo.Get_StrArrayLength(@str,‘,‘)
begin
print dbo.Get_StrArrayStrOfIndex(@str,‘,‘,@next)
set @next=@next+1
end

--6---
declare @str nvarchar(50)
declare @ia int
set @str=‘11‘
if ISNUMERIC(@str)=1
    begin
        select convert(int,@str)
    end
else
    begin
        print ‘err‘
    end

USE [WebShowDBAlter]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_SplitStr]    Script Date: 07/10/2020 17:24:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[fn_SplitStr]
(
   @SourceSql VARCHAR(max),     -- 字符串
   @StrSeprate VARCHAR(10)      -- 用于分解的字符
)
RETURNS @temp TABLE(result VARCHAR(100))
AS
BEGIN
    DECLARE @i INT
        SET @SourceSql = RTRIM(LTRIM(@SourceSql))
        SET @i=CHARINDEX(@StrSeprate,@SourceSql)
    WHILE @i>=1
    BEGIN
        INSERT @temp VALUES(LEFT(@SourceSql,@i-1))
        SET @SourceSql=SUBSTRING(@SourceSql,@i+1,LEN(@SourceSql)-@i)
        SET @i=CHARINDEX(@StrSeprate,@SourceSql)
    END
    IF @SourceSql<>‘‘
        INSERT @temp values(@SourceSql)
    RETURN
END

USE [WebShowDBAlter]
GO
/****** Object:  UserDefinedFunction [dbo].[Get_StrArrayLength]    Script Date: 07/10/2020 17:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Get_StrArrayLength]--此函数为[标量值函数]
(

@str    NVARCHAR(4000), 
@split  NVARCHAR(1000) 
)
RETURNS INT
AS
BEGIN
DECLARE @location INT
DECLARE @start INT
DECLARE @length INT
SET @str = LTRIM(RTRIM(@str))
SET @location = CHARINDEX(@split, @str)
SET @length = 1
WHILE @location <> 0
BEGIN
    SET @start = @location + 1
    SET @location = CHARINDEX(@split, @str, @start)
    SET @length = @length + 1
END
RETURN @length
END

USE [WebShowDBAlter]
GO
/****** Object:  UserDefinedFunction [dbo].[Get_StrArrayStrOfIndex]    Script Date: 07/10/2020 17:25:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Get_StrArrayStrOfIndex]
(
@str    NVARCHAR(4000), --要分割的字符串
@split  NVARCHAR(1000), --分隔符号
@index  INT --取第几个元素
)

RETURNS VARCHAR(1024)
AS
BEGIN
DECLARE @location INT
DECLARE @start INT
DECLARE @next INT
DECLARE @seed INT
SET @str = LTRIM(RTRIM(@str))
SET @start = 1
SET @next = 1
SET @seed = LEN(@split)
SET @location = CHARINDEX(@split, @str)
WHILE @location <> 0  AND @index > @next
BEGIN
    SET @start = @location + @seed
    SET @location = CHARINDEX(@split, @str, @start)
    SET @next = @next + 1
END
IF @location = 0
    SELECT @location = LEN(@str) + 1 
RETURN SUBSTRING(@str, @start, @location -@start)
END