27,580
社区成员
发帖
与我相关
我的任务
分享
CREATE FUNCTION [dbo].[f_Split_Column]
(
@fiscal_year int,
@half_index int,
@salestype varchar(50),
@tia_index varchar(50)
)
RETURNS @splittable TABLE
(
PL CHAR(4) NOT NULL --拆分后的字符串
)
AS
BEGIN
declare @str varchar(max)
declare @m int
declare @n int
declare @markStr char(1)
set @markStr=','
SELECT @str=pl FROM [SDO2].[dbo].[TIA]
WHERE FISCAL_YEAR=@fiscal_year
AND HALF_INDEX=@half_index
AND SALESTYPE=@salestype
AND TIA_INDEX=@tia_index;
if(RIGHT(@str,1)<>@markStr )
begin
set @str = @str + @markStr
end;
set @m=CHARINDEX(@markStr,@str)
set @n=0
declare @sql varchar(max)
set @sql=''
WHILE @m>0
BEGIN
INSERT INTO @splittable (PL) VALUES(substring(@str,@n,@m-@n))
set @n=@m+1
set @m=CHARINDEX(',',@str,@n)
END ;
RETURN;
END
select pl from SDO2.DBO.f_Split_Column(2009,1,'TC',1)
我看你的需求不需要用函数,这样即可.
假设你那个表为tb,列名col
select * from productlines m , tb n
where charindex(',' + n.productline_id + ',' , ',' + m.col + ',') > 0
select * from productlines m , tb n
where ',' + m.col + ',' like '%,' + n.productline_id + ',%'
select pl from SDO2.DBO.f_Split_Column(2009,1,'TC',1)