Calculeaza MAX(Camp Caracter)

SELECT MAX(CONVERT(int, dbo.STRFILTER(NumarDoc, '0123456789')))+1 FROM Intrari


-- CHRTRAN() User-Defined Function
-- Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
-- CHRTRAN (@cExpressionSearched, @cSearchExpression, @cReplacementExpression)
-- Return Values nvarchar
-- Parameters
-- @cSearchedExpression Specifies the expression in which CHRTRAN( ) replaces characters.
-- @cSearchExpression Specifies the expression containing the characters CHRTRAN( ) looks for in @cSearchedExpression.
-- @cReplacementExpression Specifies the expression containing the replacement characters.
-- If a character in cSearchExpression is found in cSearchedExpression, the character in @cSearchedExpression is replaced by a character from @cReplacementExpression
-- that is in the same position in @cReplacementExpression as the respective character in @cSearchExpression.
-- If @cReplacementExpression has fewer characters than @cSearchExpression, the additional characters in @cSearchExpression are deleted from @cSearchedExpression.
-- If @cReplacementExpression has more characters than @cSearchExpression, the additional characters in @cReplacementExpression are ignored.
-- Remarks
-- CHRTRAN() translates the character expression @cSearchedExpression using the translation expressions @cSearchExpression and @cReplacementExpression and returns the resulting character string.
-- CHRTRAN is similar to a function Oracle PL/SQL TRANSLATE
-- Example
-- select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZ') -- Displays XBYDZF
-- select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZQRST') -- Displays XBYDZF
-- See Also STRFILTER()
-- UDF the name and functionality of which correspond to the same built-in function of Visual FoxPro
/* -- this algorithm does not work as
-- select dbo.CHRTRAN2('eaba','ba','a') -- Displays e Error !!!
-- select dbo.CHRTRAN('eaba','ba','a') -- Displays ea Correctly
while @i <= @len select @cExpressionSearched = replace(cast(@cExpressionSearched as nvarchar(4000)) COLLATE Latin1_General_BIN, cast(substring(@cSearchExpression, @i, 1) as nvarchar(1)) COLLATE Latin1_General_BIN, cast(substring(@cReplacementExpression, @i, 1) as nvarchar(1)) COLLATE Latin1_General_BIN ) , @i = @i + 1 return @cExpressionSearched */ CREATE function CHRTRAN (@cExpressionSearched nvarchar(4000), @cSearchExpression nvarchar(256), @cReplacementExpression nvarchar(256)) returns nvarchar(4000) as begin declare @len smallint, @i smallint, @j smallint, @cExpressionTranslated nvarchar(4000) select @cExpressionTranslated = N'', @i = 1, @len = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode while @i <= @len begin select @j = dbo.CHARINDEX_BIN(substring(@cExpressionSearched, @i, 1), @cSearchExpression, default) if @j > 0
select @cExpressionTranslated = @cExpressionTranslated + substring(@cReplacementExpression, @j , 1)
else
select @cExpressionTranslated = @cExpressionTranslated + substring(@cExpressionSearched, @i, 1)
select @i = @i + 1
end
return @cExpressionTranslated
end
GO



-- STRFILTER() User-Defined Function
-- Removes all characters from a string except those specified.
-- STRFILTER(@cExpressionSearched, @cSearchExpression)
-- Return Values nvarchar
-- Parameters
-- @cExpressionSearched Specifies the character string to search.
-- @cSearchExpression Specifies the characters to search for and retain in cString.
-- Remarks
-- STRFILTER( ) removes all the characters from @cExpressionSearched that are not in @cSearchExpression, then returns the characters that remain.
-- Example
-- select dbo.STRFILTER('asdfghh5hh1jk6f3b7mn8m3m0m6','0123456789') -- Displays 516378306
-- select dbo.STRFILTER('ABCDABCDABCD', 'AB') -- Displays ABABAB
-- See Also CHRTRAN()
-- UDF the name and functionality of which correspond to the same functions of Foxtools ( Foxtools is a Visual FoxPro API library)
CREATE function STRFILTER (@cExpressionSearched nvarchar(4000), @cSearchExpression nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @len smallint, @i smallint, @StrFiltred nvarchar(4000)
select @StrFiltred = N'', @i = 1, @len = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode

while @i <= @len begin if dbo.CHARINDEX_BIN(substring(@cExpressionSearched, @i, 1), @cSearchExpression, default) > 0
select @StrFiltred = @StrFiltred + substring(@cExpressionSearched, @i, 1)
select @i = @i + 1
end

return @StrFiltred
end
GO

-- Is similar to the built-in function Transact-SQL charindex, but regardless of collation settings,
-- executes case-sensitive search
-- Author: Igor Nikiforov, Montreal, EMail: udfs@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