· 8 years ago · Jul 23, 2018, 08:40 AM
1namespace Common.Util
2{
3 using System;
4 using System.Collections.Generic;
5 using System.Globalization;
6 using System.Text;
7 using System.Text.RegularExpressions;
8 using System.Threading;
9
10 /// <summary>
11 /// A class that attempts to proper case a word, taking into
12 /// consideration some outliers.
13 /// </summary>
14 public class ProperCase
15 {
16 /// <summary>
17 /// Convert a string into its propercased equivalent. General case
18 /// it will capitalize the first letter of each word. Handled special
19 /// cases include names with apostrophes (O'Shea), and Scottish/Irish
20 /// surnames MacInnes, McDonalds. Will fail for Macbeth, Macaroni, etc
21 /// </summary>
22 /// <param name="inputText">The data to be recased into initial caps</param>
23 /// <returns>The input text resampled as proper cased</returns>
24 public static string Case(string inputText)
25 {
26 CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture;
27 TextInfo textInfo = cultureInfo.TextInfo;
28 string output = null;
29 int staticHack = 0;
30
31 Regex expression = null;
32 string matchPattern = string.Empty;
33
34 // Should think about maybe matching the first non blank character
35 matchPattern = @"
36 (?<Apostrophe>'.B)| # Match things like O'Shea so apostrophe plus one. Think about white space between ' and next letter. TODO: Correct it's from becoming It'S, can't -> CaN'T
37 bMac(?<Mac>.) | # MacInnes, MacGyver, etc. Will fail for Macbeth
38 bMc(?<Mc>.) # McDonalds
39 ";
40 expression = new Regex(matchPattern, RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase);
41
42 // Handle our funky rules
43 // Using named matches is probably overkill as the
44 // same rule applies to all but for future growth, I'm
45 // defining it as such.
46 // Quirky behaviour---for 2005, the compiler will
47 // make this into a static method which is verboten for
48 // safe assemblies.
49 MatchEvaluator upperCase = delegate(Match match)
50 {
51 // Based on advice from Chris Hedgate's blog
52 // I need to reference a local variable to prevent
53 // this from being turned into static
54 staticHack = matchPattern.Length;
55
56 if (!string.IsNullOrEmpty(match.Groups["Apostrophe"].Value))
57 {
58 return match.Groups["Apostrophe"].Value.ToUpper();
59 }
60
61 if (!string.IsNullOrEmpty(match.Groups["Mac"].Value))
62 {
63 return string.Format("Mac{0}", match.Groups["Mac"].Value.ToUpper());
64 }
65
66 if (!string.IsNullOrEmpty(match.Groups["Mc"].Value))
67 {
68 return string.Format("Mc{0}", match.Groups["Mc"].Value.ToUpper());
69 }
70
71 return match.Value;
72 };
73
74 MatchEvaluator evaluator = new MatchEvaluator(upperCase);
75
76 if (inputText != null)
77 {
78 // Generally, title casing converts the first character
79 // of a word to uppercase and the rest of the characters
80 // to lowercase. However, a word that is entirely uppercase,
81 // such as an acronym, is not converted.
82 // http://msdn.microsoft.com/en-us/library/system.globalization.textinfo.totitlecase(VS.80).aspx
83 string temporary = string.Empty;
84 temporary = textInfo.ToTitleCase(inputText.ToString().ToLower());
85 output = expression.Replace(temporary, evaluator);
86 }
87 else
88 {
89 output = string.Empty;
90 }
91
92 return output;
93 }
94 }
95}
96
97IF OBJECT_ID('dbo.fn_TitleCase') IS NOT NULL
98DROP FUNCTION dbo.fn_TitleCase;
99GO
100CREATE FUNCTION dbo.fn_TitleCase
101(
102 @Input nvarchar(1000)
103)
104RETURNS TABLE
105AS
106RETURN
107SELECT Item = STRING_AGG(splits.Word, ' ')
108FROM (
109 SELECT Word = UPPER(LEFT(value, 1)) + LOWER(RIGHT(value, LEN(value) - 1))
110 FROM STRING_SPLIT(@Input, ' ')
111 ) splits(Word);
112GO
113
114SELECT *
115FROM dbo.fn_TitleCase('this is a test');
116
117SELECT *
118FROM dbo.fn_TitleCase('THIS IS A TEST');
119
120CREATE FUNCTION dbo.fn_TitleCase
121(
122 @Input nvarchar(1000)
123 , @SepList nvarchar(1)
124)
125RETURNS TABLE
126AS
127RETURN
128WITH Exceptions AS (
129 SELECT v.ItemToFind
130 , v.Replacement
131 FROM (VALUES /* add further exceptions to the list below */
132 ('mca', 'McA')
133 , ('maca','MacA')
134 ) v(ItemToFind, Replacement)
135)
136, Source AS (
137 SELECT Word = UPPER(LEFT(value, 1 )) + LOWER(RIGHT(value, LEN(value) - 1))
138 , Num = ROW_NUMBER() OVER (ORDER BY GETDATE())
139 FROM STRING_SPLIT(@Input, @SepList)
140)
141SELECT Item = STRING_AGG(splits.Word, @SepList)
142FROM (
143 SELECT TOP 214748367 Word
144 FROM (
145 SELECT Word = REPLACE(Source.Word, Exceptions.ItemToFind, Exceptions.Replacement)
146 , Source.Num
147 FROM Source
148 CROSS APPLY Exceptions
149 WHERE Source.Word LIKE Exceptions.ItemToFind + '%'
150 UNION ALL
151 SELECT Word = Source.Word
152 , Source.Num
153 FROM Source
154 WHERE NOT EXISTS (
155 SELECT 1
156 FROM Exceptions
157 WHERE Source.Word LIKE Exceptions.ItemToFind + '%'
158 )
159 ) w
160 ORDER BY Num
161 ) splits;
162GO
163
164SELECT *
165FROM dbo.fn_TitleCase('THIS IS A TEST MCADAMS MACKENZIE MACADAMS', ' ');
166
167SELECT <column>,[dbo].[fProperCase](<column>,'|APT|HWY|BOX|',NULL)
168FROM <table> WHERE <column>=UPPER(<column>)
169
170CREATE FUNCTION [dbo].[fProperCase](@Value varchar(8000), @Exceptions varchar(8000),@UCASEWordLength tinyint)
171returns varchar(8000)
172as
173/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
174Function Purpose: To convert text to Proper Case.
175Created By: David Wiseman
176Website: http://www.wisesoft.co.uk
177Created: 2005-10-03
178Updated: 2006-06-22
179~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
180INPUTS:
181
182@Value : This is the text to be converted to Proper Case
183@Exceptions: A list of exceptions to the default Proper Case rules. e.g. |RAM|CPU|HDD|TFT|
184 Without exception list they would display as Ram, Cpu, Hdd and Tft
185 Note the use of the Pipe "|" symbol to separate exceptions.
186 (You can change the @sep variable to something else if you prefer)
187@UCASEWordLength: You can specify that words less than a certain length are automatically displayed in UPPERCASE
188
189USAGE1:
190
191Convert text to ProperCase, without any exceptions
192
193select dbo.fProperCase('THIS FUNCTION WAS CREATED BY DAVID WISEMAN',null,null)
194>> This Function Was Created By David Wiseman
195
196USAGE2:
197
198Convert text to Proper Case, with exception for WiseSoft
199
200select dbo.fProperCase('THIS FUNCTION WAS CREATED BY DAVID WISEMAN @ WISESOFT','|WiseSoft|',null)
201>> This Function Was Created By David Wiseman @ WiseSoft
202
203USAGE3:
204
205Convert text to Proper Case and default words less than 3 chars to UPPERCASE
206
207select dbo.fProperCase('SIMPSON, HJ',null,3)
208>> Simpson, HJ
209
210~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
211begin
212 declare @sep char(1) -- Seperator character for exceptions
213 declare @i int -- counter
214 declare @ProperCaseText varchar(5000) -- Used to build our Proper Case string for Function return
215 declare @Word varchar(1000) -- Temporary storage for each word
216 declare @IsWhiteSpace as bit -- Used to indicate whitespace character/start of new word
217 declare @c char(1) -- Temp storage location for each character
218
219 set @Word = ''
220 set @i = 1
221 set @IsWhiteSpace = 1
222 set @ProperCaseText = ''
223 set @sep = '|'
224
225 -- Set default UPPERCASEWord Length
226 if @UCASEWordLength is null set @UCASEWordLength = 1
227 -- Convert user input to lower case (This function will UPPERCASE words as required)
228 set @Value = LOWER(@Value)
229
230 -- Loop while counter is less than text lenth (for each character in...)
231 while (@i <= len(@Value)+1)
232 begin
233
234 -- Get the current character
235 set @c = SUBSTRING(@Value,@i,1)
236
237 -- If start of new word, UPPERCASE character
238 if @IsWhiteSpace = 1 set @c = UPPER(@c)
239
240 -- Check if character is white space/symbol (using ascii values)
241 set @IsWhiteSpace = case when (ASCII(@c) between 48 and 58) then 0
242 when (ASCII(@c) between 64 and 90) then 0
243 when (ASCII(@c) between 96 and 123) then 0
244 else 1 end
245
246 if @IsWhiteSpace = 0
247 begin
248 -- Append character to temp @Word variable if not whitespace
249 set @Word = @Word + @c
250 end
251 else
252 begin
253 -- Character is white space/punctuation/symbol which marks the end of our current word.
254 -- If word length is less than or equal to the UPPERCASE word length, convert to upper case.
255 -- e.g. you can specify a @UCASEWordLength of 3 to automatically UPPERCASE all 3 letter words.
256 set @Word = case when len(@Word) <= @UCASEWordLength then UPPER(@Word) else @Word end
257
258 -- Check word against user exceptions list. If exception is found, use the case specified in the exception.
259 -- e.g. WiseSoft, RAM, CPU.
260 -- If word isn't in user exceptions list, check for "known" exceptions.
261 set @Word = case when charindex(@sep + @Word + @sep,@exceptions collate Latin1_General_CI_AS) > 0
262 then substring(@exceptions,charindex(@sep + @Word + @sep,@exceptions collate Latin1_General_CI_AS)+1,len(@Word))
263 when @Word = 's' and substring(@Value,@i-2,1) = '''' then 's' -- e.g. Who's
264 when @Word = 't' and substring(@Value,@i-2,1) = '''' then 't' -- e.g. Don't
265 when @Word = 'm' and substring(@Value,@i-2,1) = '''' then 'm' -- e.g. I'm
266 when @Word = 'll' and substring(@Value,@i-3,1) = '''' then 'll' -- e.g. He'll
267 when @Word = 've' and substring(@Value,@i-3,1) = '''' then 've' -- e.g. Could've
268 else @Word end
269
270 -- Append the word to the @ProperCaseText along with the whitespace character
271 set @ProperCaseText = @ProperCaseText + @Word + @c
272 -- Reset the Temp @Word variable, ready for a new word
273 set @Word = ''
274 end
275 -- Increment the counter
276 set @i = @i + 1
277 end
278 return @ProperCaseText
279end