-- Author:  Igor Nikiforov,  Montreal,  EMail: udfunctions@gmail.com 
 -- GETALLWORDS2() User-Defined Function Inserts the words from a string into the table.
 -- GETALLWORDS2(@cString[, @cStringSplitting])
 -- Parameters
 -- @cString  nvarchar(4000) - Specifies the string whose words will be inserted into the table @GETALLWORDS2. 
 -- @cStringSplitting nvarchar(256) - Optional. Specifies the string used to separate words in @cString.
 -- The default delimiter is space.
 -- Note that GETALLWORDS2( ) uses  @cStringSplitting as a single delimiter. 
 -- Return Value table 
 -- Remarks GETALLWORDS2() by default assumes that words are delimited by space. If you specify another string as delimiter, this function ignores spaces and uses only the specified string.
 -- Example
 -- declare @cString nvarchar(4000), @nIndex smallint 
 -- select @cString = 'We hold these truths to be self-evident, that all men are created equal, that they are endowed by their Creator with certain unalienable Rights, that among these are Life, Liberty and the pursuit of Happiness.', @nIndex = 30
 -- select WORD from dbo.GETALLWORDS2(@cString, default) where WORDNUM = @nIndex  -- Displays 'Liberty'
 -- select top 1 WORDNUM from dbo.GETALLWORDS2(@cString, default) order by WORDNUM desc  -- Displays 35
 -- See Also GETWORDNUM() , GETWORDCOUNT() ,  GETALLWORDS()  User-Defined Functions   
CREATE function GETALLWORDS2  (@cString nvarchar(4000), @cStringSplitting  nvarchar(256) = ' '  )   -- if no break string is specified, the function uses space to delimit words.
returns  @GETALLWORDS2  table (WORDNUM  smallint, WORD nvarchar(4000), STARTOFWORD smallint, LENGTHOFWORD  smallint)
    begin
        declare @k smallint,   @BegOfWord smallint,  @wordcount  smallint,  @nEndString smallint,  @nLenSrtingSplitting smallint, @flag bit

        select   @cStringSplitting = isnull(@cStringSplitting, space(1)) ,
                    @cString = isnull(@cString, '') ,
                    @BegOfWord = 1,   @wordcount = 1,  @k = 0 , @flag = 0,
                    @nEndString =  1+  datalength(@cString) /(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2  else 1 end),
                    @nLenSrtingSplitting =  datalength(@cStringSplitting) /(case SQL_VARIANT_PROPERTY(@cStringSplitting,'BaseType') when 'nvarchar' then 2  else 1 end)   -- for unicode

       while  1 > 0 
          begin
             if  @k - @BegOfWord  >  0  
                 begin
                      insert into @GETALLWORDS2 (WORDNUM,  WORD, STARTOFWORD, LENGTHOFWORD)    values( @wordcount,  substring(@cString,  @BegOfWord , @k - @BegOfWord ) , @BegOfWord,  @k - @BegOfWord)
                      select  @wordcount = @wordcount + 1,  @BegOfWord = @k 
                 end 

             if  @flag  = 1 
                 break

             while charindex( substring(@cString, @BegOfWord, @nLenSrtingSplitting) COLLATE Latin1_General_BIN, @cStringSplitting COLLATE Latin1_General_BIN) > 0 --  skip break strings, if any     
                 set  @BegOfWord  = @BegOfWord +  @nLenSrtingSplitting

             select   @k  = charindex(@cStringSplitting  COLLATE Latin1_General_BIN, @cString COLLATE Latin1_General_BIN, @BegOfWord)   

             if  @k = 0 
                select   @k  =  @nEndString,  @flag  = 1
          end

       return 
    end
GO

CREATE FUNCTION dbo.fnSplitDelimitedString
(
	@Text VARCHAR(8000),
	@Delimiter VARCHAR(8000)
)
RETURNS @Parts TABLE
	(
		i SMALLINT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,
		Part VARCHAR(8000)
	)
AS

BEGIN
	DECLARE	@LastIndex SMALLINT,
		@NextIndex SMALLINT

	IF @Text IS NULL OR DATALENGTH(@Text) = 0
		RETURN

	IF @Delimiter IS NULL
		SELECT	@Delimiter = ';'

	SELECT	@LastIndex = 0,
		@NextIndex = 1

	WHILE @NextIndex > 0
		BEGIN
			SELECT	@NextIndex = CHARINDEX(@Delimiter, @Text, @LastIndex + 1)

			INSERT	@Parts
				(
					Part
				)
			SELECT	CASE
					WHEN @NextIndex = 0 THEN SUBSTRING(@Text, @LastIndex + 1, DATALENGTH(@Text) - @LastIndex)
					ELSE SUBSTRING(@Text, @LastIndex + 1, @NextIndex - @LastIndex - 1)
				END

			SELECT	@LastIndex = @NextIndex
		END

	RETURN
END

CREATE FUNCTION dbo.fnSplitFixedString
(
	@Text VARCHAR(8000),
	@PartLength SMALLINT,
	@WantedStart SMALLINT,
	@WantedLength SMALLINT
)
RETURNS @Parts TABLE
	(
		i SMALLINT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,
		Part VARCHAR(8000)
	)
AS

BEGIN
	DECLARE	@Index SMALLINT

	IF @PartLength < 1 OR @PartLength IS NULL
		SELECT @PartLength = 1

	IF @PartLength > DATALENGTH(@Text)
		SELECT @PartLength = DATALENGTH(@Text)

	IF @WantedStart < 1 OR @WantedStart IS NULL
		SELECT @WantedStart = 1

	IF @WantedStart > @PartLength
		SELECT @WantedStart = @PartLength

	IF @WantedLength < 1 OR @WantedLength IS NULL
		SELECT @WantedLength = 1

	IF @WantedLength > @PartLength - @WantedStart + 1
		SELECT @WantedLength = @PartLength - @WantedStart + 1

	SELECT	@Index = 0

	WHILE @Index < DATALENGTH(@Text) / @PartLength
		BEGIN
			INSERT	@Parts
				(
					Part
				)
			SELECT	SUBSTRING(@Text, @WantedStart + @Index * @PartLength, @WantedLength)

			SELECT	@Index = @Index + 1
		END

	RETURN
END