sql分割函数及数据遍历

最新推荐文章于 2022-10-26 15:08:47 发布

jiwei964879217

最新推荐文章于 2022-10-26 15:08:47 发布

阅读量427

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/jiwei964879217/article/details/108004434

版权

sql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

CREATE FUNCTION sqlSPLIT( @Long_str varchar ( 8000 ), @split_str varchar ( 100 ))
RETURNS @tmp TABLE (
ID inT IDENTITY PRIMARY KEY ,
short_str varchar ( 8000 )
)
AS
BEGIN
DECLARE @long_str_Tmp varchar ( 8000 ), @short_str varchar ( 8000 ), @split_str_length int
SET @split_str_length = LEN ( @split_str )
IF CHARINDEX ( @split_str , @Long_str ) = 1
SET @long_str_Tmp = SUBSTRING ( @Long_str , @split_str_length + 1 , LEN ( @Long_str ) - @split_str_length )
ELSE
SET @long_str_Tmp = @Long_str
IF CHARINDEX ( REVERSE ( @split_str ), REVERSE ( @long_str_Tmp )) > 1
SET @long_str_Tmp = @long_str_Tmp + @split_str
ELSE
SET @long_str_Tmp = @long_str_Tmp
IF CHARINDEX ( @split_str , @long_str_Tmp ) = 0
Insert INTO @tmp select @long_str_Tmp
ELSE
BEGIN
WHILE CHARINDEX ( @split_str , @long_str_Tmp ) > 0
BEGIN
SET @short_str = SUBSTRING ( @long_str_Tmp , 1 , CHARINDEX ( @split_str , @long_str_Tmp ) - 1 )
DECLARE @long_str_Tmp_LEN INT , @split_str_Position_END int
SET @long_str_Tmp_LEN = LEN ( @long_str_Tmp )
SET @split_str_Position_END = LEN ( @short_str ) + @split_str_length
SET @long_str_Tmp = REVERSE ( SUBSTRING ( REVERSE ( @long_str_Tmp ), 1 , @long_str_Tmp_LEN - @split_str_Position_END ))
IF @short_str <> '' Insert INTO @tmp select @short_str
END
END
RETURN
END

--数据遍历

declare @tmp_fshno table (fshno nvarchar(60))
declare @fshno varchar(1000)
declare cursor_2 cursor for --
select fshno from tqmaddscrap where prostatus='2' and fshno is not null and creattime>'2020-08-10'
open cursor_2
fetch next from cursor_2 into @fshno
while @@FETCH_STATUS=0
begin
insert into @tmp_fshno select short_str from sqlSPLIT(@fshno,',')

--提取下一条数据
fetch next from cursor_2 into @fshno
end

--关闭游标
close cursor_2

--释放游标
deallocate cursor_2
select * from @tmp_fshno