tisdag 20 mars 2012

T-SQL Split function

Split function to find the Nth word in a string

--select dbo.Split('abc,def', ',',2) --returns def


Create FUNCTION [dbo].[Split] (@fullString varchar(200), @separator char(1),@wordNo tinyint)
RETURNS VARCHAR(200) -- return_data_type.
AS BEGIN
declare @wordPart varchar(200),
              @sepPos int=0,
              @wordCount int = 1

while @wordCount <= @wordNo
begin
      if @fullString = ''
            return null

      set @sepPos = CHARINDEX(@separator, @fullString, @sepPos) if @sepPos = 0set @sepPos = len(@fullString)
      set @wordPart = SUBSTRING(@fullString, 1, @sepPos)set @fullString = REPLACE(@fullString, @wordPart,'')
      set @wordCount = @wordCount +1

end

RETURN replace(@wordPart,',','')

END

Inga kommentarer:

Skicka en kommentar