To create those type of functions in the SQL database, start SQL Query Analyzer, copy and past, execute it by pressing F5.
User-Defined Functions Transact-SQL, the name and functionality of which correspond to the same built-in functions of Visual FoxPro: GETWORDCOUNT, GETWORDNUM, AT, RAT, OCCURS, PADC, PADR, PADL, PROPER , and User-Defined Function Transact-SQL RCHARINDEX which is similar to the built-in function charindex but the search of which is on the right. In my humble opinion, it will be convinient to apply such functions for writing of stored procedures and User-Defined Functions on Transact-SQL.
The above mentioned functions work similarly as case sensitive functions irrespective of installations of Collation Settings in your database, i.e. the symbols 'A' and 'a' differ as in the same functions in Visual FoxPro.
GETWORDCOUNT() Counts the words in a string
GETWORDNUM() Returns a specified word from a string
AT() Returns the beginning numeric position of the first occurrence of a character expression within
another character expression, counting from the leftmost character
RAT() Returns the numeric position of the last (rightmost) occurrence of a character string within
another character string
OCCURS() Returns the number of times a character expression occurs within another character expression
PADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left side
PADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right side
PADC() Returns a string from an expression, padded with spaces or characters to a specified length on the both sides
PROPER() Returns from a character expression a string capitalized as appropriate for proper names
RCHARINDEX() Is similar to a built-in function Transact-SQL charindex but the search of which is on the right
-- UDFs string functions Transact-SQL similar to built-in string functions FoxPro
-- GETWORDCOUNT, GETWORDNUM, AT, RAT, OCCURS, PADC, PADR, PADL, PROPER
-- GETWORDCOUNT() Counts the words in a string
-- GETWORDNUM() Returns a specified word from a string
-- AT() Returns the beginning numeric position of the first occurrence of a character expression within
-- another character expression, counting from the leftmost character
-- RAT() Returns the numeric position of the last (rightmost) occurrence of a character string within
-- another character string
-- OCCURS() Returns the number of times a character expression occurs within another character expression
-- PADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left side
-- PADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right side PADC() Returns a string from an expression, padded with spaces or characters to a specified length on the both sides
-- PROPER() Returns from a character expression a string capitalized as appropriate for proper names
-- RCHARINDEX() Is similar to a built-in function Transact-SQL charindex but the search of which is on the right
-- 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
-- select dbo.PADC (' Transact-SQL FoxPro', 60, '*')
-- UDF the name and functionality of which correspond to the same built-in functions of Visual FoxPro
-- Author: Igor Nikiforov, Montreal, EMail: nikiforov@sympatico.ca
-- AT() User-Defined Function
-- Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character.
--
-- AT(@cSearchExpression, @cExpressionSearched [, @nOccurrence])
-- Return Values smallint
--
-- Parameters
-- @cSearchExpression nvarchar(4000) Specifies the character expression that AT( ) searches for in @cExpressionSearched.
-- @cExpressionSearched nvarchar(4000) Specifies the character expression @cSearchExpression searches for.
-- @nOccurrence smallint Specifies which occurrence (first, second, third, and so on) of @cSearchExpression is searched for in @cExpressionSearched. By default, AT() searches for the first occurrence of @cSearchExpression (@nOccurrence = 1). Including @nOccurrence lets you search for additional occurrences of @cSearchExpression in @cExpressionSearched. AT( ) returns 0 if @nOccurrence is greater than the number of times @cSearchExpression occurs in @cExpressionSearched.
-- Remarks
-- AT() searches the second character expression for the first occurrence of the first character expression. It then returns an integer indicating the position of the first character in the character expression found. If the character expression isn't found, AT() returns 0. The search performed by AT() is case-sensitive.
--
-- Example
-- declare @gcString nvarchar(4000), @gcFindString nvarchar(4000)
-- select @gcString = 'Now is the time for all good men', @gcFindString = 'is the'
-- select dbo.AT(@gcFindString, @gcString) -- Displays 5
-- set @gcFindString = 'IS'
-- select dbo.AT(@gcFindString,@gcString) -- Displays 0, case-sensitive
-- See Also RAT() User-Defined Function
--
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
-- UDF the name and functionality of which correspond to the same built-in functions of Visual FoxPro
-- Author: Igor Nikiforov, Montreal, EMail: nikiforov@sympatico.ca
-- RAT( ) User-Defined Function
-- Returns the numeric position of the last (rightmost) occurrence of a character string within another character string.
--
-- RAT(@cSearchExpression, @cExpressionSearched [, @nOccurrence])
-- Return Values smallint
--
-- Parameters
-- @cSearchExpression nvarchar(4000) Specifies the character expression that RAT( ) looks for in @cExpressionSearched.
-- @cExpressionSearched nvarchar(4000) Specifies the character expression that RAT() searches.
-- @nOccurrence smallint Specifies which occurrence, starting from the right and moving left, of @cSearchExpression RAT() searches for in @cExpressionSearched. By default, RAT() searches for the last occurrence of @cSearchExpression (@nOccurrence = 1). If @nOccurrence is 2, RAT() searches for the next to last occurrence, and so on.
-- Remarks
-- RAT(), the reverse of the AT() function, searches the character expression in @cExpressionSearched starting from the right and moving left, looking for the last occurrence of the string specified in @cSearchExpression.
--
-- RAT() returns an integer indicating the position of the first character in @cSearchExpression in @cExpressionSearched. RAT() returns 0 if @cSearchExpression isn't found in @cExpressionSearched, or if @nOccurrence is greater than the number of times @cSearchExpression occurs in @cExpressionSearched.
-- The search performed by RAT() is case-sensitive.
--
-- Example
-- declare @gcString nvarchar(4000), @gcFindString nvarchar(4000)
-- select @gcString = 'abracadabra', @gcFindString = 'a'
-- select dbo.RAT(@gcFindString , @gcString ) -- Displays 11
-- select dbo.RAT(@gcFindString , @gcString , 3) -- Displays 6
-- See Also AT() User-Defined Function
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
-- UDF the name and functionality of which correspond to the same built-in functions of Visual FoxPro
-- Author: Igor Nikiforov, Montreal, EMail: nikiforov@sympatico.ca
-- PADL(), PADR(), PADC() User-Defined Functions
-- Returns a string from an expression, padded with spaces or characters to a specified length on the left or right sides, or both.
-- PADL(@eExpression, @nResultSize [, @cPadCharacter]) -Or-
-- PADR(@eExpression, @nResultSize [, @cPadCharacter]) -Or-
-- PADC(@eExpression, @nResultSize [, @cPadCharacter])
-- Return Values nvarchar(4000)
--
-- Parameters
-- @eExpression nvarchar(4000) Specifies the expression to be padded.
-- @nResultSize smallint Specifies the total number of characters in the expression after it is padded.
-- @cPadCharacter nvarchar(4000) Specifies the value to use for padding. This value is repeated as necessary to pad the expression to the specified number of characters.
-- If you omit @cPadCharacter, spaces (ASCII character 32) are used for padding.
--
-- Remarks
-- PADL() inserts padding on the left, PADR() inserts padding on the right, and PADC() inserts padding on both sides.
--
-- Example
-- declare @gcString nvarchar(4000)
-- select @gcString = 'TITLE'
-- select dbo.PADL(@gcString, 40, default)
-- select dbo.PADL(@gcString, 40, '=')
-- select dbo.PADR(@gcString, 40, '=')
-- select dbo.PADC(@gcString, 40, '=')
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
-- UDF the name and functionality of which correspond to the same built-in functions of Visual FoxPro
-- Author: Igor Nikiforov, Montreal, EMail: nikiforov@sympatico.ca
-- PADL(), PADR(), PADC() User-Defined Functions
-- Returns a string from an expression, padded with spaces or characters to a specified length on the left or right sides, or both.
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
-- UDF the name and functionality of which correspond to the same built-in functions of Visual FoxPro
-- Author: Igor Nikiforov, Montreal, EMail: nikiforov@sympatico.ca
-- PADL(), PADR(), PADC() User-Defined Functions
-- Returns a string from an expression, padded with spaces or characters to a specified length on the left or right sides, or both.
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
-- UDF the name and functionality of which correspond to the same built-in functions of Visual FoxPro
-- Author: Igor Nikiforov, Montreal, EMail: nikiforov@sympatico.ca
-- OCCURS() User-Defined Function
-- Returns the number of times a character expression occurs within another character expression.
--
-- OCCURS(@cSearchExpression, @cExpressionSearched)
-- Return Values smallint
--
-- Parameters
-- @cSearchExpression nvarchar(4000) Specifies a character expression that OCCURS() searches for within @cExpressionSearched.
-- @cExpressionSearched nvarchar(4000) Specifies the character expression OCCURS() searches for @cSearchExpression.
-- Remarks
-- OCCURS() returns 0 (zero) if @cSearchExpression isn't found within @cExpressionSearched.
--
-- Example
-- declare @gcString nvarchar(4000)
-- select @gcString = 'abracadabra'
-- select dbo.OCCURS('a', @gcString ) -- Displays 5
-- select dbo.OCCURS('b', @gcString ) -- Displays 2
-- select dbo.OCCURS('c', @gcString ) -- Displays 1
-- select dbo.OCCURS('e', @gcString ) -- Displays 0
-- See Also AT(), RAT()
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
-- UDF the name and functionality of which correspond to the same built-in functions of Visual FoxPro
-- Author: Igor Nikiforov, Montreal, EMail: nikiforov@sympatico.ca
-- PROPER( ) User-Defined Function
-- Returns from a character expression a string capitalized as appropriate for proper names.
--
-- PROPER(@cExpression)
-- Return Values nvarchar(4000)
--
-- Parameters
-- @cExpression nvarchar(4000) Specifies the character expression from which PROPER( ) returns a capitalized character string.
-- Example
-- declare @gcExpr1 nvarchar(4000), @gcExpr2 nvarchar(4000)
-- select @gcExpr1 = 'Visual Basic.NET', @gcExpr2 = 'VISUAL BASIC.NET'
-- select dbo.PROPER(@gcExpr1) -- Displays 'Visual Basic.net'
-- select dbo.PROPER(@gcExpr2) -- Displays 'Visual Basic.net'
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
-- UDF the name and functionality of which correspond to the same built-in functions of Visual FoxPro
-- Author: Igor Nikiforov, Montreal, EMail: nikiforov@sympatico.ca
-- GETWORDCOUNT() User-Defined Function
-- Counts the words in a string.
--
-- GETWORDCOUNT(@cString[, @cDelimiters])
-- Parameters
-- @cString nvarchar(4000) - Specifies the string whose words will be counted.
-- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString.
-- The default delimiters are space, tab, carriage return, and line feed. Note that GETWORDCOUNT( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter.
-- Return Value smallint
--
-- Remarks
-- GETWORDCOUNT() by default assumes that words are delimited by spaces or tabs. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.
--
-- If you use "AAA aaa, BBB bbb, CCC ccc." as the target string for dbo.GETWORDCOUNT(), you can get all the following results.
-- declare @cString nvarchar(4000)
-- set @cString = "AAA aaa, BBB bbb, CCC ccc."
-- select dbo.GETWORDCOUNT(@cString, default) -- 6 - character groups, delimited by " "
-- select dbo.GETWORDCOUNT(@cString, ",") -- 3 - character groups, delimited by ","
-- select dbo.GETWORDCOUNT(@cString, ".") -- 1 - character group, delimited by "."
-- See Also GETWORDNUM() User-Defined Function
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
-- UDF the name and functionality of which correspond to the same built-in functions of Visual FoxPro
-- Author: Igor Nikiforov, Montreal, EMail: nikiforov@sympatico.ca
-- GETWORDNUM() User-Defined Function
-- Returns a specified word from a string.
--
-- GETWORDNUM(@cString, @nIndex[, @cDelimiters])
-- Parameters
-- @cString nvarchar(4000) - Specifies the string to be evaluated
-- @nIndex smallint - Specifies the index position of the word to be returned. For example, if @nIndex is 3, GETWORDNUM( ) returns the third word (if @cString contains three or more words).
-- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString. The default delimiters are space, tab, carriage return, and line feed. Note that GetWordNum( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter.
-- Return Value nvarchar(4000)
--
-- Remarks
-- Returns the word at the position specified by @nIndex in the target string, @cString. If @cString contains fewer than @nIndex words, GETWORDNUM( ) returns an empty string.
--
-- See Also
-- GETWORDCOUNT() User-Defined Function
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 the built-in function Transact-SQL charindex but the search of which is 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 the built-in function Transact-SQL charindex, but regardless of collation settings,
-- executes case-sensitive search
-- 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
