Upper/lower case text

Question

Upper/lower case text

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

Jeff Moden (1/14/2011)
Lowell (1/4/2011)
this is the ProperCase function I use; it's from a post by Jeff Moden, i believe, harvested long long ago.

This may be the source....

http://qa.sqlservercentral.com/Forums/FindPost644446.aspx

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Jeff Moden (1/14/2011)
Wow! I just took a closer look at the code above. If I actually was the one who wrote that, I'm awfully sorry. :sick: I must have been under the influence of some pretty nasty cold medicine or something. It doesn't even work for words with more than one leading space and I'm pretty sure I would use STUFF nowadays instead of overloading a variable like that. The formatting in the CTE's isn't my typical formatting and I'm almost religious about using 2 part naming conventions when creating SQL objects. I'm also fairly religious about aliasing the Tally table as "t" and I don't believe I've ever used "Tally.N" in my code... its always (AFAIR) just "t.N".
If it really is mine, give me the link so I can go back and fix it because it's absolutely terrible.

I take it back. It does work for multiple leading spaces. The stuff that looks like spaces in sys.syscomments in the GRID mode are actually spaces... most of the are ^C_r^L_f characters

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

mister.magoo (1/14/2011)
Jeff Moden (1/14/2011)
Lowell (1/4/2011)
this is the ProperCase function I use; it's from a post by Jeff Moden, i believe, harvested long long ago.
This may be the source....
http://qa.sqlservercentral.com/Forums/FindPost644446.aspx

Thanks, Magoo...

That's a whole lot cleaner code but I still don't believe it's mine because I just don't use "Tally.N" anywhere. The truth is, I believe that Lowell came up with that slice of computational heaven on his own and forgot about it simply because it used a Tally Table.

So, nice job Lowell! 🙂 Really nice job because I don't know what the heck I wrote before for where I said the loop beat it. And, if I find it, I won't post it because it must've been pretty bad. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Ok... if I had to write such a thing today, here's how I'd probably write it... it's pretty fast, too, although I'm sure someone will come up with a way to turn it into a much faster iTVF. 🙂

CREATE FUNCTION dbo.ProperCase_JBM

/***************************************************************************************************

Purpose:

Capitalize letters that follow a space.

Rev History:

Rev 00 - 14 Jan 2011 - Jeff Moden - Initial Creation

***************************************************************************************************/

--===== Define I/O parameters

(

@pString VARCHAR(8000)

)

RETURNS VARCHAR(8000)

WITH SCHEMABINDING

AS

BEGIN

--===== Change all characters to lower case

DECLARE @CleanedText VARCHAR(8000)

;

SELECT @CleanedText = LOWER(@pString)

;

--===== "Inline" CTE Driven "Tally Table" produces values up to

-- 10,000... enough to cover VARCHAR(8000)

WITH

E1(N) AS ( --=== Create Ten 1's

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 --10

),

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000

cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4)

--===== Find and capitalize only where a letter follows a space

SELECT @CleanedText = STUFF(@CleanedText, t.N, 1, UPPER(SUBSTRING(@CleanedText, t.N, 1)))

FROM cteTally t

WHERE t.N BETWEEN 1 AND LEN(@pString)

AND (

SUBSTRING(@CleanedText,t.N-1,2) LIKE '[ ][a-z]' COLLATE Latin1_General_Bin

OR

t.N = 1

)

;

RETURN @CleanedText

;

END

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions