Je voudrais proposer a votre attention des fonctions Transact-SQL semblables aux fonctions homonymes Visual FoxPro
AT, RAT, PADC, PADL, PADR, OCCURS, PROPER, GETWORDNUM, GETWORDCOUNT
et une fonction Transact-SQL RCHARINDEX
Exemple:
select dbo.GETWORDCOUNT('User-Defined Functions', default)
select dbo.GETWORDNUM('User-Defined Functions', 3, default)
select dbo.PROPER(Je voudrais proposer a votre attention des fonctions')
et cetera
Le code creant a n'importe quelle base de donnees User-Defined Functions
------------------------------------------------------------------------
-- Is similar to a function of a like function FoxPro
-- Author: Igor Nikiforov, Montreal, EMail: nikiforov@sympatico.ca
CREATE function AT (@cSearchExpression varchar(8000), @cExpressionSearched varchar(8000), @nOccurrence smallint = 1 )
returns smallint
as
begin
if @nOccurrence > 0
begin
declare @i smallint, @StartingPosition smallint
select @i = 1, @StartingPosition = charindex(@cSearchExpression, @cExpressionSearched)
while @StartingPosition <> 0 and @nOccurrence > @i
select @i = @i + 1, @StartingPosition = charindex(@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 varchar(8000), @cExpressionSearched varchar(8000), @nOccurrence smallint = 1 )
returns smallint
as
begin
if @nOccurrence > 0
begin
declare @i smallint, @length smallint, @StartingPosition smallint
select @i = 1, @length = datalength(@cExpressionSearched), @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 - dbo.LENSPACE(@cSearchExpression) + 1
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 varchar(8000), @nLen smallint, @cPadCharacter varchar(8000) = ' ' )
returns varchar(8000)
as
begin
if datalength(@cSrting) >= @nLen
set @cSrting = left(@cSrting, @nLen)
else
begin
declare @nLeftLen smallint, @nRightLen smallint
set @nLeftLen = (@nLen - datalength(@cSrting) )/2 -- Quantity of characters, added at the left
set @nRightLen = @nLen - datalength(@cSrting) - @nLeftLen -- Quantity of characters, added on the right
set @cSrting = left(replicate(@cPadCharacter, ceiling(@nLeftLen/datalength(@cPadCharacter)) + 2), @nLeftLen)+ @cSrting + left(replicate(@cPadCharacter, ceiling(@nRightLen/datalength(@cPadCharacter)) + 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 varchar(8000), @nLen smallint, @cPadCharacter varchar(8000) = ' ' )
returns varchar(8000)
as
begin
if datalength(@cSrting) >= @nLen
set @cSrting = left(@cSrting, @nLen)
else
begin
declare @nLeftLen smallint, @nRightLen smallint
set @nLeftLen = @nLen - datalength(@cSrting) -- Quantity of characters, added at the left
set @cSrting = left(replicate(@cPadCharacter, ceiling(@nLeftLen/datalength(@cPadCharacter)) + 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 varchar(8000), @nLen smallint, @cPadCharacter varchar(8000) = ' ' )
returns varchar(8000)
as
begin
if datalength(@cSrting) >= @nLen
set @cSrting = left(@cSrting, @nLen)
else
begin
declare @nRightLen smallint
set @nRightLen = @nLen - datalength(@cSrting) -- Quantity of characters, added on the right
set @cSrting = @cSrting + left(replicate(@cPadCharacter, ceiling(@nRightLen/datalength(@cPadCharacter)) + 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 varchar(8000), @cExpressionSearched varchar(8000))
returns smallint
as
begin
declare @start_location smallint, @location smallint, @occurs smallint
set @start_location = CHARINDEX(@cSearchExpression, @cExpressionSearched)
if @start_location > 0
begin
select @occurs = 1, @location = @start_location
while @location > 0
begin
set @location = CHARINDEX(@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 varchar(8000))
returns varchar(8000)
as
begin
declare @i smallint, @j smallint, @start_location smallint, @lenword smallint, @wordcount smallint, @word varchar(8000), @Delimiters varchar(8000), @properexpression varchar(8000), @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 = charindex(@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)
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, datalength(@expression) - @j + 1)
end
GO
-- Is similar to a function of a like function FoxPro
-- Author: Igor Nikiforov, Montreal, EMail: nikiforov@sympatico.ca
CREATE function GETWORDCOUNT (@cSrting varchar(8000), @cDelimiters varchar(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, @end_of_string = datalength(@cSrting) + 1, @wordcount = 0
while charindex(substring(@cSrting, @p, 1), @cDelimiters)>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 charindex(substring(@cSrting, @p, 1), @cDelimiters)>0
and charindex(substring(@cSrting, @p+1, 1), @cDelimiters) = 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 varchar(8000), @nIndex smallint, @cDelimiters varchar(256) )
returns varchar(8000)
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, @outstr varchar(8000)
select @i = 1, @p = 1, @q = 0, @end_of_string = datalength(@cSrting) + 1, @outstr = ''
while @i <= @nIndex
begin
while charindex(substring(@cSrting, @p, 1), @cDelimiters)>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 <= datalength(@cDelimiters)
begin
set @q = charindex(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 varchar(8000), @expression2 varchar(8000), @start_location smallint = 1 )
returns varchar(8000)
as
begin
declare @StartingPosition smallint
set @StartingPosition = charindex( reverse(@expression1), reverse(@expression2), @start_location )
return case
when @StartingPosition > 0
then datalength(@expression2) + 1 - @StartingPosition
else 0
end
end
GO
------------------------------------------------------------------------
