Set = 'ALL UPPER CASE and SOME lower ÄÄ ÖÖ ÜÜ ÉÉ ØØ ĈĈ ÆÆ' Really, any low/up character combination could be inserted into and the statement would not change: declare nvarchar(8000)ĭeclare table (low nchar(1), up nchar(1))
Tony introduces the concept of an exception / break character to deal with these cases.Īnyways, building on Cervo's idea (upper all lower chars preceded by space), the replace statements could be wrapped up in a single table based replace instead. The link I posted above is a great option that addresses the main issue: that we can never programmatically account for all cases (Smith-Jones, von Haussen, John Smith M.D.), at least not in an elegant manner. Others' comments that different solutions are preferable for non-English input remain the case. While <= + case when = 1 or and or and and ') then else = case when like '' then 0 when then -1 else 1 = + 1 This modification keeps apostrophe-s as lowercase where the s comes at the end of the string provided or the s is followed by a space (and only in those circumstances). RETURN slight modification to answer - which turns e.g. N ' should not capatolize the letter following the apostrophy */ OR SubString( n-1, 1 ) LIKE '' /* the previous character is NOT a letter */ N = 1 /* this character is the first `character` */ SELECT (1 + n1.n + n10.n + n100.n + n1000.n) AS nįROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS n1ĬROSS JOIN (SELECT 0 AS n UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90) AS n10ĬROSS JOIN (SELECT 0 AS n UNION SELECT 100 UNION SELECT 200 UNION SELECT 300 UNION SELECT 400 UNION SELECT 500 UNION SELECT 600 UNION SELECT 700 UNION SELECT 800 UNION SELECT 900) AS n100ĬROSS JOIN (SELECT 0 AS n UNION SELECT 1000 UNION SELECT 2000 UNION SELECT 3000) AS n1000ĪND SubString( n, 1 ) LIKE '' /* this character is a letter */ SELECT = Stuff( n, 1, Upper( SubString( n, 1 ) ) ) Using a Sequence, replace the letters that should be upper case with their upper case version If you want just white-space (space, tab, carriage-return, line-feed), you can replace the pattern '' with ''.ĬREATE FUNCTION String.InitCap( nvarchar(4000) ) RETURNS nvarchar(4000) ASĭECLARE nvarchar(4000) SET = 2. This does how ever mean that 123apple would have a match on the "a" because "3" is not a letter. I have just included a simple set that will upper case any letter that is proceeded by a non-letter with the exception of apostrophes. You can modify the WHERE clause to suite your personal rules for when to convert a character to upper case. Here is a version that uses a sequence or numbers table rather than a loop. Select dbo.fnConvert_TitleCase(upper('Whatever the mind of man can conceive and believe, it can achieve. RETURN calls: select dbo.fnConvert_TitleCase(Upper('ÄÄ ÖÖ ÜÜ ÉÉ ØØ ĈĈ ÆÆ')) as test I know this is late post in this thread but, worth looking. RETURN dbo.ToProperCase('ALL UPPER CASE and SOME lower ÄÄ ÖÖ ÜÜ ÉÉ ØØ ĈĈ ÆÆ')Īll Upper Case and Some lower Ää Öö Üü Éé Øø Cc Ææ white space definition can be configuredĬREATE FUNCTION VARCHAR(255)) RETURNS VARCHAR(255)ĭECLARE VARCHAR(10) - characters considered as white space.
#CHANGE CASE IN WORD 2017 PORTABLE#
is portable in that it does not use fancy features of recent SQL server versions.works properly even for non-English alphabets."Proper Cases" all "UPPER CASE" words that are delimited by white space.
0 kommentar(er)
