I've had a lot of good feedback about the options that we've provided in our free SDU Tools for developers and DBAs for formatting strings. Someone recently asked for a format with all capitals and dashes in between. I realised we didn't have that, and we've added it. Generally, in the industry, this is referred to as Cobol Case.
So, we added a function CobolCase.
It takes one parameter:
@InputString – the string to be formatted
Find out more
You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:
You can use our tools as a set or as a great example of how to write functions like these.
Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:
http://sdutools.sqldownunder.com
Latest version of the code
Note: the code might wrap when displayed below.
|
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 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
CREATE OR ALTER FUNCTION SDU_Tools.CobolCase ( @InputString nvarchar(max) ) RETURNS nvarchar(max) AS BEGIN -- Function: Apply Cobol Casing to a string -- Parameters: @InputString varchar(max) -- Action: Apply Cobol Casing to a string (similar to programming identifiers) -- Return: nvarchar(max) -- Refer to this video: https://youtu.be/i1rnVlOR760 -- -- Test examples: /* SELECT SDU_Tools.CobolCase(N'the quick brown fox consumed a macrib at mcdonalds'); SELECT SDU_Tools.CobolCase(N'janet mcdermott'); SELECT SDU_Tools.CobolCase(N'the case of sherlock holmes and the curly-Haired company'); */ DECLARE @Response nvarchar(max) = N''; DECLARE @StringToProcess nvarchar(max); DECLARE @CharacterCounter int = 0; DECLARE @WordCounter int = 0; DECLARE @Character nchar(1); DECLARE @InAWord bit; DECLARE @CurrentWord nvarchar(max); DECLARE @NumberOfWords int; DECLARE @Words TABLE ( WordNumber int IDENTITY(1,1), Word nvarchar(max) ); SET @StringToProcess = LOWER(LTRIM(RTRIM(@InputString))); SET @InAWord = 0; SET @CurrentWord = N''; WHILE @CharacterCounter < LEN(@StringToProcess) BEGIN SET @CharacterCounter += 1; SET @Character = SUBSTRING(@StringToProcess, @CharacterCounter, 1); IF @Character IN (N' ', N'-', NCHAR(9)) -- whitespace or hyphens BEGIN IF @InAWord <> 0 BEGIN SET @InAWord = 0; INSERT @Words VALUES (@CurrentWord); SET @CurrentWord = N''; END; END ELSE BEGIN -- not whitespace IF @InAWord = 0 -- start of a word BEGIN SET @InAWord = 1; SET @CurrentWord = @Character; END ELSE BEGIN -- part of a word SET @CurrentWord += @Character; END; END; END; IF @InAWord <> 0 BEGIN INSERT @Words VALUES (@CurrentWord); END; SET @NumberOfWords = (SELECT COUNT(*) FROM @Words); SET @WordCounter = 0; WHILE @WordCounter < @NumberOfWords BEGIN SET @WordCounter += 1; SET @CurrentWord = (SELECT Word FROM @Words WHERE WordNumber = @WordCounter); SET @Response += CASE WHEN @WordCounter > 1 THEN N'-' ELSE N'' END + @CurrentWord; END; RETURN UPPER(@Response); END; GO |