SQL Server 2014 - Capitalize first letter with exceptions

by Joe   Last Updated December 07, 2017 10:06 AM

I need to properly format some european addresses one of the steps is to capitalize first letter, but avoiding some specific words, like 'on' 'upon' 'von' 'van' 'di' 'in' 'sul' therefore, although my skills are scarse, I thought was a good Idea to use a regex based function:

googleing I found this here:

    @String nvarchar(max)
    RETURNS nvarchar(max)
        DECLARE @Position INT;

            @String   = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
            @Position = PATINDEX('%[^A-Za-z][a-z]%',@String COLLATE Latin1_General_Bin);

        WHILE @Position > 0
                @String   = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
                @Position = PATINDEX('%[^A-Za-z][a-z]%',@String COLLATE Latin1_General_Bin);

        RETURN @String;

that seems to search for a sequence of a 'non letter' + a lowercase 'letter'


OK, I think I've understood how it works, and I modify it to best match my needs:

I supposed it was better to search for a space or ' or - and a lowercase letter, therefore I changed to


then, after several attempt, I built this regex on regexr.com, that (seems) to catch the required sequence


but when I put into the above function, the result is not as expected..

what's wrong?

Tags : sql-server regex

Related Questions

Validate web url string in MongoDB 3.4

Updated August 08, 2017 14:06 PM

Display a field using regex

Updated November 09, 2016 08:02 AM