[Feature Request] INITCAP Function

[edgeerrol87]

Hi,

This query runs :

SELECT INITCAP('ABC COMPANY')
UNION ALL
SELECT INITCAP('Dean o''brian')
UNION ALL
SELECT TOP 10 INITCAP(fullname) FROM contact

It should be great to have this feature too on Sql4Cds...

Here my code C#

/// <summary>
/// Returns a character expression with character data converted to proper case or title case.
/// Handles single quotes within words correctly.
/// </summary>
/// <param name="value">An expression of character data</param>
/// <returns></returns>
[CollationSensitive]
public static SqlString InitCap([MaxLength] SqlString value)
{
    if (value.IsNull)
        return value;

    // Using StringBuilder with initial capacity for better performance
    var sb = new StringBuilder(value.Value.Length);

    var textInfo = value.CultureInfo.TextInfo;
    var words = value.Value.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

    for (int i = 0; i < words.Length; i++)
    {
        var parts = words[i].Split(new[] { '\'' }, StringSplitOptions.RemoveEmptyEntries);
        for (int j = 0; j < parts.Length; j++)
        {
            var part = parts[j].ToLower(value.CultureInfo);
            sb.Append(j > 0 && part.Length == 1 ? part : textInfo.ToTitleCase(part));
            if (j < parts.Length - 1)
            {
                sb.Append('\'');
            }
        }

        if (i < words.Length - 1)
        {
            sb.Append(' ');
        }
    }

    return new SqlString(sb.ToString(), value.LCID, value.SqlCompareOptions);
}

Unit test

[DataTestMethod]
[DataRow("SELECT INITCAP('   Andrew-le-Smith')", "Andrew-Le-Smith")]
[DataRow("SELECT INITCAP('BOBs-your-Uncle  ')", "Bobs-Your-Uncle")]
[DataRow("SELECT INITCAP('Carl-VAN')", "Carl-Van")]
[DataRow("SELECT INITCAP('EDDY   thompson')", "Eddy Thompson")]
[DataRow("SELECT INITCAP('Eddy thompson')", "Eddy Thompson")]
[DataRow("SELECT INITCAP('Dean o''brian')", "Dean O'Brian")]
[DataRow("SELECT INITCAP('ben''s PIeS')", "Ben's Pies")]
[DataRow("SELECT INITCAP('i-thINK-this    soLUTION-works-LiKe-a charm')", "I-Think-This Solution-Works-Like-A Charm")]
[DataRow("SELECT INITCAP('   sPark sql     ')", "Spark Sql")]
[DataRow("SELECT INITCAP('opal kole')", "Opal Kole")]
[DataRow("SELECT INITCAP('ABC COMPANY')", "Abc Company")]
[DataRow("SELECT INITCAP('DEF PLASTICITY')", "Def Plasticity")]
[DataRow("SELECT INITCAP('stackoverflow com')", "Stackoverflow Com")]
[DataRow("SELECT INITCAP('''O''HEARN')", "O'Hearn")]
[DataRow("SELECT INITCAP('DAVE''S VALUE')", "Dave's Value")]
[DataRow("SELECT INITCAP('eLI LEiba knows SQL')", "Eli Leiba Knows Sql")]
public void InitCap(string query, string expected)
{
    using (var con = new Sql4CdsConnection(_localDataSources))
    using (var cmd = con.CreateCommand())
    {
        cmd.CommandText = query;
        var actual = (string)cmd.ExecuteScalar();
        Assert.AreEqual(expected, actual);
    }
}

Please advise.

[MarkMpn]

Thanks for the details!

INITCAP seems to be an Oracle-specific function. I'm trying to keep SQL 4 CDS compatible with SQL Server, but one thing I'd like to investigate is allowing you to add your own user-defined functions via the SQL CLR, so I might keep this for a future release to include as a great example of how to do that.