C# string function to T-SQL scalar function

Question

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#?

Anthony Faull · Accepted Answer · 2011-07-13 13:38:50Z

3

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

answered Jul 13, 2011 at 13:38

Anthony Faull

18.1k5 gold badges58 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daniel Hilgarth · Accepted Answer · 2011-07-13 11:25:13Z

2

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.

answered Jul 13, 2011 at 11:25

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

Collectives™ on Stack Overflow

C# string function to T-SQL scalar function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related