
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_sxstr_brand]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_sxstr_brand]
GO

CREATE FUNCTION fn_sxstr_brand(@str_range varchar(255),@item_tag char(1))
returns  varchar(255)

AS 
 
BEGIN 
	declare @para varchar(20),
			@pos int,
			@pos_old int,
			@pos1 int,
			@pos2 int,
			@start int,
			@item_clsname varchar(20),
			@item_brandname varchar(20),
			@item_name varchar(40),
			@str_cls varchar(255)
			
			set @str_cls = ''					
			set @pos = CHARINDEX('|',@str_range)
			set @pos_old = 0			
			while @pos > 1 --分割数据符号|(1)
			begin
				if @pos_old = 0		--拆出单个
					set @para = SUBSTRING(@str_range,1 ,@pos - 1) 
				else
					set @para = SUBSTRING(@str_range,@pos_old + 1 ,@pos - @pos_old - 1) 
				if isnull(@para,'')<>''		
					begin
						if @item_tag = '2' --品牌券
							begin
								select @item_brandname = code_name from t_bd_base_code where type_no = 'PP' and code_id = @para
								if isnull(@item_brandname,'')<>''	
									begin
									if @str_cls = '' 
										set @str_cls =  @item_brandname +'|'
									else
										set @str_cls =  @str_cls +@item_brandname + '|'
									end
							end
					end
				set @pos_old = @pos
				set @start = @pos + 1
				set @pos = CHARINDEX('|',@str_range,@start)--查找下一个
			end 

			RETURN	@str_cls		
END   
GO


