This is useful for importing data.
When bulk inserting into a single column staging table it is easy to get the columns if they are fixed length.
This gives a simple (although not efficient) method of getting the columns from a csv file.


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

Create function f_GetEntryDelimiitted
	(
	@line varchar(4000) ,
	@fldnum int ,
	@delim varchar(10) ,
	@quoted varchar(1)	-- Y/N
	)
returns varchar(400)
as
begin
/*
create table #a (s varchar(1000))
insert #a select '"John","Smith","IT",2,"sql",1'
insert #a select '"Fred","Jones","Assassin",7,"Guns",5'
insert #a select '"Peter","Brown","Politics",23,minister,5'

select a.* from
(
select 	Forname		= dbo.f_GetEntryDelimiitted (s, 1, ',', 'Y') ,
	Surname 	= dbo.f_GetEntryDelimiitted (s, 2, ',', 'Y') ,
	Profession 	= dbo.f_GetEntryDelimiitted (s, 3, ',', 'Y') ,
	ProfessionExp 	= dbo.f_GetEntryDelimiitted (s, 4, ',', 'Y') ,
	Skill 		= dbo.f_GetEntryDelimiitted (s, 5, ',', 'Y') ,
	SkillExp 	= dbo.f_GetEntryDelimiitted (s, 6, ',', 'Y')
from #a
) a

drop table #a
*/

declare	@i int ,
	@j int ,
	@delimUsed varchar(11) ,
	@s varchar(4000)

	select @i = 1
	while	@fldnum > 0
	begin
		select @fldnum = @fldnum - 1
		if substring(@line, @i, 1) = '"' and @Quoted = 'Y'
		begin
			
			select 	@delimUsed = '"' + @Delim ,
				@i = @i + 1
		end
		else
		begin
			select @delimUsed = @Delim
		end
		
		select @j = charindex(@delimUsed, @line, @i)
		if @j = 0
			select @j = datalength(@line) + 1
		if @fldnum > 0
			select @i = @j +len(@delimused)
	end
	
	select @s = substring(@line, @i, @j - @i)
	return	@s
end

go