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'
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..