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:

CREATE FUNCTION InitialCap
(
    @String nvarchar(max)
)
    RETURNS nvarchar(max)
AS
    BEGIN 
        DECLARE @Position INT;

        SELECT 
            @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
            SELECT 
                @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;
    END

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

[^A-Za-z][a-z]

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

[\s'-][\w]

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

[\s](?!di\s|in\s|sul\s|on\s|upon\s|von\s|uber\s|ueber\s)[\w]

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


How to split numbers and text in MySQL

Updated July 10, 2015 13:02 PM