[SQL]分號分隔的字串轉成TABLE

  • 1026
  • 0

摘要:[SQL]分號分隔的字串轉成TABLE


ALTER FUNCTION [dbo].[fnc_GetCsvTable](@Parameter varchar(MAX))
returns @TMP_TABLE table (chrCol varchar(150))
as
begin

	declare @iRow int;
	declare @chrData varchar(100);

	set @Parameter = @Parameter + ';'
	set @iRow = 1;
	set @chrData = '';

	while @iRow <= len(@Parameter)
		begin			
			if substring(@Parameter,@iRow,1) = ';'
				begin		
					if @chrData<>''		
						insert into @TMP_TABLE(chrCol) values (rtrim(ltrim(@chrData)))							
						
					set @chrData = ''					
				end
			else
				set @chrData = @chrData + substring(@Parameter,@iRow,1)				
			set @iRow = @iRow +1			
		end
	return
end