“Why doesn’t this function exist?”
We have all had the situation where there is a need for a little custom function to modify strings or do simple math on a column. Today I am going to show how to create a User Defined Function (UDF) in Microsoft SQL.
The function will allow you to put a space before Upper case (capital), letters. The function will read the input string character by character and insert spaces before a capital letter. Additionally the function provides the option to not a space between consecutive upper case letters, i.e SQL.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
CREATE FUNCTION dbo.SpaceBeforeCapital ( @InputString NVARCHAR(MAX) , @PreserveAdjacentCaps BIT ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @i INT , @j INT , @previous NCHAR , @current NCHAR , @next NCHAR , @result NVARCHAR(MAX) SELECT @i = 1 , @j = LEN(@InputString) , @result = '' WHILE @i <= @j BEGIN SELECT @previous = SUBSTRING(@InputString,@i-1,1) , @current = SUBSTRING(@InputString,@i+0,1) , @next = SUBSTRING(@InputString,@i+1,1) IF @current = UPPER(@current) COLLATE Latin1_General_CS_AS BEGIN -- Add space if Current is UPPER -- and either Previous or Next is lower or user chose not to preserve adjacent caps -- and Previous or Current is not already a space IF @current = UPPER(@current) COLLATE Latin1_General_CS_AS AND ( @previous <> UPPER(@previous) COLLATE Latin1_General_CS_AS OR @next <> UPPER(@next) collate Latin1_General_CS_AS OR @PreserveAdjacentCaps = 0 ) AND @previous <> ' ' AND @current <> ' ' SET @result = @result + ' ' END SET @result = @result + @current SET @i = @i + 1 END RETURN @result END |
And the way to call the function is via a SELECT statement is as follows
|
1 2 3 4 5 6 7 8 9 10 |
-- preserve adjacent upper case letters SELECT dbo.SpaceBeforeCap('ThisIsASampleDBString', 1) -- spaces between adjacent upper case letters SELECT dbo.SpaceBeforeCap('ThisIsASampleDBString', 0) -- preserve adjacent upper case letters SELECT dbo.SpaceBeforeCap([YourFieldName], 1) FROM dbo.YourTableName ; |
Leave a Reply