下面函数的功能是：[email protected]@str。
[email protected]，[email protected][email protected]，谢谢！！
测试用例：
DECLARE @machnos varchar(100)
SET @machnos='AB-01,AB-02,AB-04,AB-05,AB-06,AB-07,AB-08,AB-09,AB-10,AB-11,AB-12,AB-13,AB-14,AB-15,AB-16,AB-17,AB-28,AB-19,AB-20,AB-21,AB-22,AB-23,AB-24,AB-29,AB-32,AB-33,AB-34'
SELECT * FROM SplitStr(@machnos,',')
-----------------------------------------------
--自定义函数
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[SplitStr](@str nvarchar(max),@split nvarchar(100))
returns @t Table(col nvarchar(1000))
as begin
declare @i int
declare @s int
set @i=1
set @s=1
while(@i>0)
begin
set @i=charindex(@split,@str,@s)
if(@i>0)
begin
insert @t(col) values(substring(@str,@s,@[email protected]))
end
else begin
insert @t(col) values(substring(@str,@s,len(@str)[email protected]+1))
end
set @s = @i + 1
end
return
end
----------------------------------------------
------解决方案--------------------
你定义的时候就应该 DECLARE @machnos varchar(8000)
------解决方案--------------------
- SQL code
把 @split 定义为 nvarchar(max)