-- Functions Transact-SQL similar to function FoxPro
-- Examples:   GETWORDCOUNT, GETWORDNUM
-- select  dbo.GETWORDCOUNT('Functions Transact-SQL similar to function FoxPro', default)
-- select  dbo.GETWORDNUM('Functions Transact-SQL similar to function FoxPro', 2, default)
-- AT, RAT, OCCURS, PROPER  
-- select  dbo.AT ('FoxPro', 'Functions Transact-SQL similar to function FoxPro', default)
-- select  dbo.OCCURS ('F', 'Functions Transact-SQL similar to function FoxPro')
-- select  dbo.PROPER ('Functions Transact-SQL similar to function FoxPro')
-- PADC, PADR, PADL 
-- Is similar to a function of a like function FoxPro
-- Author:  Igor Nikiforov,  Montreal,  EMail: nikiforov@sympatico.ca   
CREATE function AT  (@cSearchExpression nvarchar(4000), @cExpressionSearched  nvarchar(4000), @nOccurrence  smallint = 1 )
returns smallint
as
    begin
      if @nOccurrence > 0
         begin
            declare @i smallint,  @StartingPosition  smallint
            select  @i = 1, @StartingPosition  = dbo.CHARINDEX_BIN(@cSearchExpression, @cExpressionSearched, 1)
            while  @StartingPosition <> 0 and @nOccurrence > @i
               select  @i = @i + 1, @StartingPosition  = dbo.CHARINDEX_BIN(@cSearchExpression, @cExpressionSearched,  @StartingPosition+1 )
         end
      else
         set @StartingPosition =  NULL

     return @StartingPosition
    end
GO

-- Is similar to a function of a like function FoxPro
-- Author:  Igor Nikiforov,  Montreal,  EMail: nikiforov@sympatico.ca   
CREATE function RAT  (@cSearchExpression nvarchar(4000), @cExpressionSearched  nvarchar(4000), @nOccurrence  smallint = 1 )
returns smallint
as
    begin
      if @nOccurrence > 0
         begin
            declare @i smallint, @length smallint, @StartingPosition  smallint
            select  @i = 1, @length  = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode
            select  @StartingPosition  = dbo.RCHARINDEX(@cSearchExpression, @cExpressionSearched, 1)
            while @StartingPosition <> 0 and @nOccurrence > @i
               select  @i = @i + 1, @StartingPosition  = dbo.RCHARINDEX(@cSearchExpression, @cExpressionSearched,  @length - @StartingPosition + 2 )
         end
      if @StartingPosition <> 0
         select @StartingPosition = @StartingPosition + 1  - datalength(@cSearchExpression)/(case SQL_VARIANT_PROPERTY(@cSearchExpression,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode
      else
         set @StartingPosition =  NULL

     return @StartingPosition
    end
GO

-- Is similar to a function of a like function FoxPro
-- Author:  Igor Nikiforov,  Montreal,  EMail: nikiforov@sympatico.ca   
CREATE function PADC  (@cSrting nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )
returns nvarchar(4000)
as
      begin
        declare @length smallint, @lengthPadCharacter smallint
        select  @length  = datalength(@cSrting)/(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode
        select  @lengthPadCharacter  = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode

	if @length >= @nLen
	   set  @cSrting = left(@cSrting, @nLen)
	else
	   begin
              declare @nLeftLen smallint, @nRightLen smallint
              set @nLeftLen = (@nLen - @length )/2            -- Quantity of characters, added at the left
              set @nRightLen  =  @nLen - @length - @nLeftLen  -- Quantity of characters, added on the right
              set @cSrting = left(replicate(@cPadCharacter, ceiling(@nLeftLen/@lengthPadCharacter) + 2), @nLeftLen)+ @cSrting + left(replicate(@cPadCharacter, ceiling(@nRightLen/@lengthPadCharacter) + 2), @nRightLen)
	   end

     return (@cSrting)
    end
GO

-- Is similar to a function of a like function FoxPro
-- Author:  Igor Nikiforov,  Montreal,  EMail: nikiforov@sympatico.ca   
CREATE function PADL  (@cSrting nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )
returns nvarchar(4000)
as
      begin
        declare @length smallint, @lengthPadCharacter smallint
        select  @length = datalength(@cSrting)/(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode
        select  @lengthPadCharacter = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode

        if @length >= @nLen
           set  @cSrting = left(@cSrting, @nLen)
        else
	   begin
              declare @nLeftLen smallint,  @nRightLen smallint
              set @nLeftLen = @nLen - @length  -- Quantity of characters, added at the left
              set @cSrting = left(replicate(@cPadCharacter, ceiling(@nLeftLen/@lengthPadCharacter) + 2), @nLeftLen)+ @cSrting
           end

    return (@cSrting)
   end
GO

-- Is similar to a function of a like function FoxPro
-- Author:  Igor Nikiforov,  Montreal,  EMail: nikiforov@sympatico.ca   
CREATE function PADR  (@cSrting nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )
returns nvarchar(4000)
as
     begin
       declare @length smallint, @lengthPadCharacter smallint
       select  @length  = datalength(@cSrting)/(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode
       select  @lengthPadCharacter  = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode

       if @length >= @nLen
          set  @cSrting = left(@cSrting, @nLen)
       else
          begin
             declare  @nRightLen smallint
             set @nRightLen  =  @nLen - @length -- Quantity of characters, added on the right
             set @cSrting =  @cSrting + left(replicate(@cPadCharacter, ceiling(@nRightLen/@lengthPadCharacter) + 2), @nRightLen)
	  end

     return (@cSrting)
    end
GO

-- Is similar to a function of a like function FoxPro
-- Author:  Igor Nikiforov,  Montreal,  EMail: nikiforov@sympatico.ca   
-- Returns the number of times a character expression occurs within another character expression.
CREATE function OCCURS  (@cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000))
returns smallint
as
    begin
      declare @start_location smallint, @location smallint, @occurs  smallint
      set @start_location = dbo.CHARINDEX_BIN(@cSearchExpression, @cExpressionSearched, 1)

     if @start_location > 0
        begin
	   select @occurs = 1, @location = @start_location
	   while @location > 0
	      begin
	         set @location = dbo.CHARINDEX_BIN(@cSearchExpression, @cExpressionSearched, @start_location+1)
	         if @location > 0
	            select  @occurs = @occurs + 1, @start_location = @location
	      end
        end
      else
         set  @occurs = 0

     return  @occurs
    end
GO

-- Is similar to a function of a like function FoxPro
-- Author:  Igor Nikiforov,  Montreal,  EMail: nikiforov@sympatico.ca   
CREATE function PROPER  (@expression nvarchar(4000))
returns nvarchar(4000)
as
    begin
      declare @i  smallint, @j  smallint, @start_location smallint, @lenword smallint, @wordcount  smallint,  @word   nvarchar(4000),  @Delimiters  nvarchar(4000), @properexpression nvarchar(4000), @cDelimiters char(7)
      set @cDelimiters = char(9)+char(10)+char(11)+char(12)+char(13)+char(32)+char(160) -- Separators of words in a function FoxPro PROPER
      select  @wordcount = dbo.GETWORDCOUNT(@expression, @cDelimiters), @start_location = 1, @i = 1, @j = 1, @properexpression = ''
    
      while  @i <= @wordcount
          begin
             set @word = dbo.GETWORDNUM(@expression, @i, @cDelimiters)            -- Word
             set @start_location = dbo.CHARINDEX_BIN(@word, @expression, @start_location) -- The stand with which one starts a word
             set @Delimiters = substring(@expression, @j, @start_location - @j)   -- Interval between words
             set @lenword = datalength(@word)/(case SQL_VARIANT_PROPERTY(@word,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode
             set @properexpression = @properexpression + @Delimiters + upper(left(@word,1))+lower(substring(@word, 2, @lenword-1 )) 
             select @i = @i + 1, @start_location = @start_location + @lenword
             set @j = @start_location
          end

     return @properexpression + substring(@expression, @j,  1 - @j + datalength(@expression)/(case SQL_VARIANT_PROPERTY(@expression,'BaseType') when 'nvarchar' then 2  else 1 end) )
    end
GO

-- Is similar to a function of a like function FoxPro
-- Author:  Igor Nikiforov,  Montreal,  EMail: nikiforov@sympatico.ca   
CREATE function GETWORDCOUNT  (@cSrting nvarchar(4000), @cDelimiters nvarchar(256) )
returns smallint 
as
    begin
      -- if no break string is specified, the function uses spaces, tabs and line feed to delimit words.
      set @cDelimiters = isnull(@cDelimiters, space(1)+char(9)+char(10))
      declare @p smallint, @end_of_string smallint, @wordcount smallint
      select  @p = 1, @wordcount = 0
      select  @end_of_string = 1 + datalength(@cSrting)/(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode

      while dbo.CHARINDEX_BIN(substring(@cSrting, @p, 1), @cDelimiters, 1) > 0  and  @end_of_string > @p  -- skip opening break characters, if any
          set @p = @p + 1

      if @p < @end_of_string
         begin
            set @wordcount = 1 -- count the one we are in now
            -- count transitions from "not in word" to "in word" .if the current character is a break char,
	    -- but the next one is not, we've entered a new word.
            while @p < @end_of_string
               begin
                  if @p +1 < @end_of_string  and dbo.CHARINDEX_BIN(substring(@cSrting, @p, 1), @cDelimiters, 1) > 0
                                              and dbo.CHARINDEX_BIN(substring(@cSrting, @p+1, 1), @cDelimiters, 1) = 0
                     begin
                        set @wordcount = @wordcount + 1
                        set @p = @p + 1 -- Skip over the first character in the word. We know it can't be a break character.
                     end
                  set @p = @p + 1
               end
         end

     return @wordcount
    end
GO

-- Is similar to a function of a like function FoxPro
-- Author:  Igor Nikiforov,  Montreal,  EMail: nikiforov@sympatico.ca   
CREATE function GETWORDNUM  (@cSrting nvarchar(4000), @nIndex smallint, @cDelimiters nvarchar(256) )
returns nvarchar(4000)
as
    begin
      -- if no break string is specified, the function uses spaces, tabs and line feed to delimit words.
      set @cDelimiters = isnull(@cDelimiters,  space(1)+char(9)+char(10))
      declare @i smallint,  @j smallint, @p smallint, @q smallint, @qmin smallint, @end_of_string smallint, @LenDelimiters smallint, @outstr  nvarchar(4000)
      select  @i = 1, @p = 1, @q = 0, @outstr = ''
      select  @end_of_string = 1 + datalength(@cSrting)/(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode
      select  @LenDelimiters = datalength(@cDelimiters)/(case SQL_VARIANT_PROPERTY(@cDelimiters,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode

      while @i <= @nIndex
         begin
            while dbo.CHARINDEX_BIN(substring(@cSrting, @p, 1), @cDelimiters, 1) > 0 and  @end_of_string > @p   -- skip opening break characters, if any
               set @p = @p + 1

            if  @p >= @end_of_string
               break

            select @j = 1, @qmin = @end_of_string -- find next break character it marks the end of this word
            while @j <= @LenDelimiters
               begin
                  set @q = dbo.CHARINDEX_BIN(substring(@cDelimiters, @j, 1), @cSrting, @p)
                  set @j = @j + 1
                  if @q > 0 and @qmin > @q
                     set @qmin = @q
               end

            if @i = @nIndex -- this is the actual word we are looking for.
               begin
                  set @outstr =  substring(@cSrting, @p, @qmin-@p)
                  break
               end
             set @p = @qmin + 1

             if (@p >= @end_of_string)
                 break
             set @i = @i + 1
         end

     return @outstr
    end
GO

-- Is similar to a function charindex, but executes looking up on the right
-- Author:  Igor Nikiforov,  Montreal,  EMail: nikiforov@sympatico.ca   
CREATE function RCHARINDEX  (@expression1 nvarchar(4000), @expression2  nvarchar(4000), @start_location  smallint = 1 )
returns nvarchar(4000)
as
    begin
       declare @StartingPosition  smallint
       set  @StartingPosition = dbo.CHARINDEX_BIN( reverse(@expression1), reverse(@expression2), @start_location)

     return case 
               when  @StartingPosition > 0
               then  1 - @StartingPosition + datalength(@expression2)/(case SQL_VARIANT_PROPERTY(@expression2,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode  
            else 0 
            end
    end
GO

-- Is similar to a function charindex, but regardless of collation settings,  
-- executes case-sensitive looking up 
-- Author:  Igor Nikiforov,  Montreal,  EMail: nikiforov@sympatico.ca   
CREATE function CHARINDEX_BIN(@expression1 nvarchar(4000), @expression2  nvarchar(4000), @start_location  smallint = 1)
returns nvarchar(4000)
as
    begin
       return charindex( cast(@expression1 as nvarchar(4000)) COLLATE Latin1_General_BIN, cast(@expression2  as nvarchar(4000)) COLLATE Latin1_General_BIN, @start_location )
    end
GO