数据库将字符串转换为多行

来源：互联网发布：烟台seo整站优化方案编辑：程序博客网时间：2024/06/04 18:22

sql server数据库：

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_StrToTable]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_StrToTable]
GO
Create Function fn_StrToTable(@str varchar(max))
Returns @tableName Table
(
goodsid char(11)
)
As
Begin
set @str = @str+','
Declare @insertStr char(11) --截取后的第一个字符串
Declare @newstr varchar(max) --截取第一个字符串后剩余的字符串
set @insertStr = left(@str,charindex(',',@str)-1)
set @newstr = stuff(@str,1,charindex(',',@str),'')
Insert @tableName Values(@insertStr)
while(len(@newstr)>0)
begin
set @insertStr = left(@newstr,charindex(',',@newstr)-1)
Insert @tableName Values(@insertStr)
set @newstr = stuff(@newstr,1,charindex(',',@newstr),'')
end
Return
End

Oracle数据库：

with a as (select ',G0ZL08F4EIS,G09OHEQ2DLI,G0BK8OCHSBY' id from dual)
select regexp_substr(id,'[^,]+',1,rownum) goodsid from a
connect by rownum<=length(regexp_replace(id,'[^,]+'))

0 0