C# string function to T-SQL scalar function
I want to use a c# function which modifies a string for html valid url name. The function is like below:
public static string HTMLValidName(string input)
{
string[] pattern = new string[] { "[^a-zA-Z0-9-]", "-+" };
string[] replacements = new string[] { "-", "-" };
input = input.Trim();
input = input.Replace("Ç", "C");
input = input.Replace("ç", "c");
input = input.Replace("Ğ", "G");
input = input.Replace("ğ", "g");
input = input.Replace("Ü", "U");
input = input.Replace("ü", "u");
input = input.Replace("Ş", "S");
input = input.Replace("ş", "s");
input = input.Replace("İ", "I");
input = input.Replace("ı", "i");
input = input.Replace("Ö", "O");
input = input.Replace("ö", "o");
for (int i = 0; i <= pattern.Length - 1; i++)
input = Regex.Replace(input, pattern[i], replacements[i]);
while(input.Contains("--"))
{
input = input.Replace("--", "-");
}
if (input[0] == '-') input = input.Substring(1, input.Length - 1);
return input;
}
I need to use this function for SQL results. Like SELECT ID FROM Categories WHERE HTMLValidName(Title)=@URLTitle
How can I convert this to a T-SQL function or can I create this function in c#开发者_StackOverflow社区?
CREATE FUNCTION dbo.HtmlValidName(@input nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @i int
DECLARE @match int
-- Remove diacritical marks
SET @input = CAST(@input AS varchar(max)) COLLATE Japanese_BIN
-- Replace non-alphanumerics with dash
SET @i = 0
WHILE @i < 1000000
BEGIN
SET @match = PATINDEX('%[^a-zA-Z0-9-]%', @input)
IF @match = 0 BREAK
SET @input = STUFF(@input, @match, 1, '-')
SET @i = @i + 1
END
RETURN @input
END
You can create a managed user defined function that runs in your SQL server and looks from the outside like a normal user defined function. See here for more info.
精彩评论