Upper/lower case text

Question

Post reply

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]

sc-w Hall of Fame Points: 3664 More actions · Answer 1

I nearly missed that post, i tried the following but it generates an error. Any thoughts?

update dbo.wce_contact set firstname = dbo.ProperCase(firstname)

Error:

Msg 1014, Level 15, State 1, Line 1

TOP clause contains an invalid value.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

That has to be something else. Are you using a view?

Is the view working all by itself in a simple select statement?

GSquared SSC Guru Points: 260824 More actions · Answer 3

Jeff:

I did a speed test like this:

SET NOCOUNT ON;

go

IF OBJECT_ID(N'tempdb..#Strings') IS NOT NULL

DROP TABLE #Strings;

IF OBJECT_ID(N'tempdb..#T1') IS NOT NULL

DROP TABLE #T1;

IF OBJECT_ID(N'tempdb..#T2') IS NOT NULL

DROP TABLE #T2;

IF OBJECT_ID(N'tempdb..#T3') IS NOT NULL

DROP TABLE #T3;

CREATE TABLE #Strings (

String VARCHAR(MAX));

GO

INSERT INTO #Strings

( String )

VALUES ( 'You know me... I''ll always be one of the first to come up with a Tally Table solution and (now) to avoid scalar UDF''s like the plague. However, I''ve always said the Tally Table isn''t a panacea and not all rules about avoiding scalar UDF''s are absolutely cut''n''dry.' -- String - varchar(max)

);

go 100000

SET STATISTICS TIME ON;

SELECT

(SELECT CASE

WHEN ASCII(ChPrior) BETWEEN 65 AND 90 THEN LOWER(Ch)

WHEN ASCII(ChPrior) BETWEEN 97 AND 122 THEN LOWER(Ch)

ELSE UPPER(Ch)

END

FROM

(SELECT SUBSTRING(String, Number, 1) AS Ch, SUBSTRING(String, Number-1, 1) AS ChPrior, Number AS Seq

FROM dbo.Numbers

WHERE Number <= LEN(String)) AS Parser

ORDER BY Seq

FOR XML PATH(''), TYPE).value('.', 'varchar(max)') AS ICap

INTO #T1

FROM #Strings;

SELECT dbo.InitialCap(String) AS ICap

INTO #T2

FROM #Strings;

SELECT dbo.ProperCase(String) AS ICap

INTO #T3

FROM #Strings;

SET STATISTICS TIME OFF;

/*Beginning execution loop

Batch execution completed 1000 times.

SQL Server Execution Times:

CPU time = 31 ms, elapsed time = 31 ms.

SQL Server Execution Times:

CPU time = 703 ms, elapsed time = 734 ms.

SQL Server Execution Times:

CPU time = 391 ms, elapsed time = 390 ms.

Beginning execution loop

Batch execution completed 10000 times.

SQL Server Execution Times:

CPU time = 156 ms, elapsed time = 203 ms.

SQL Server Execution Times:

CPU time = 7000 ms, elapsed time = 7328 ms.

SQL Server Execution Times:

CPU time = 3766 ms, elapsed time = 3765 ms.

Beginning execution loop

Batch execution completed 100000 times.

SQL Server Execution Times:

CPU time = 1797 ms, elapsed time = 2109 ms.

SQL Server Execution Times:

CPU time = 74500 ms, elapsed time = 79921 ms.

SQL Server Execution Times:

CPU time = 41406 ms, elapsed time = 42203 ms.*/

The results are in the comments at the end. All I did was change the GO iteration each time. The non-Tally/Numbers solution took the longest of all three solutions each time on that test data. And not by small margins. How are you testing it?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sc-w Hall of Fame Points: 3664 More actions · Answer 4

It's not a view, it's a real table in my SQL Express 2008 database.

Lowell SSC Guru Points: 323482 More actions · Answer 5

sc-w (1/4/2011)
I nearly missed that post, i tried the following but it generates an error. Any thoughts?
update dbo.wce_contact set firstname = dbo.ProperCase(firstname)
Error:
Msg 1014, Level 15, State 1, Line 1
TOP clause contains an invalid value.

does the table dbo.wce_contact have a trigger on it? look at the body of the trigger...i'm guessing it is using the TOP command, which implies it might not be written as well as it could be.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

GSquared (1/4/2011)
Jeff:
I did a speed test like this:
SET NOCOUNT ON;
go
IF OBJECT_ID(N'tempdb..#Strings') IS NOT NULL
DROP TABLE #Strings;
IF OBJECT_ID(N'tempdb..#T1') IS NOT NULL
DROP TABLE #T1;
IF OBJECT_ID(N'tempdb..#T2') IS NOT NULL
DROP TABLE #T2;
IF OBJECT_ID(N'tempdb..#T3') IS NOT NULL
DROP TABLE #T3;
CREATE TABLE #Strings (
String VARCHAR(MAX));
GO
INSERT INTO #Strings
( String )
VALUES ( 'You know me... I''ll always be one of the first to come up with a Tally Table solution and (now) to avoid scalar UDF''s like the plague. However, I''ve always said the Tally Table isn''t a panacea and not all rules about avoiding scalar UDF''s are absolutely cut''n''dry.' -- String - varchar(max)
);
go 100000
SET STATISTICS TIME ON;
SELECT
(SELECT CASE
WHEN ASCII(ChPrior) BETWEEN 65 AND 90 THEN LOWER(Ch)
WHEN ASCII(ChPrior) BETWEEN 97 AND 122 THEN LOWER(Ch)
ELSE UPPER(Ch)
END
FROM
(SELECT SUBSTRING(String, Number, 1) AS Ch, SUBSTRING(String, Number-1, 1) AS ChPrior, Number AS Seq
FROM dbo.Numbers
WHERE Number <= LEN(String)) AS Parser
ORDER BY Seq
FOR XML PATH(''), TYPE).value('.', 'varchar(max)') AS ICap
INTO #T1
FROM #Strings;
SELECT dbo.InitialCap(String) AS ICap
INTO #T2
FROM #Strings;
SELECT dbo.ProperCase(String) AS ICap
INTO #T3
FROM #Strings;
SET STATISTICS TIME OFF;
/*Beginning execution loop
Batch execution completed 1000 times.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 31 ms.
SQL Server Execution Times:
CPU time = 703 ms, elapsed time = 734 ms.
SQL Server Execution Times:
CPU time = 391 ms, elapsed time = 390 ms.
Beginning execution loop
Batch execution completed 10000 times.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 203 ms.
SQL Server Execution Times:
CPU time = 7000 ms, elapsed time = 7328 ms.
SQL Server Execution Times:
CPU time = 3766 ms, elapsed time = 3765 ms.
Beginning execution loop
Batch execution completed 100000 times.
SQL Server Execution Times:
CPU time = 1797 ms, elapsed time = 2109 ms.
SQL Server Execution Times:
CPU time = 74500 ms, elapsed time = 79921 ms.
SQL Server Execution Times:
CPU time = 41406 ms, elapsed time = 42203 ms.*/
The results are in the comments at the end. All I did was change the GO iteration each time. The non-Tally/Numbers solution took the longest of all three solutions each time on that test data. And not by small margins. How are you testing it?

The only difference is my test data. I'm using it against syscomments which uses spaces way heavier than normal text. So that may explain why I'm getting much slower results with your code. Other than that, the loop code from Jeff is also way slower than the tally version.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Lowell (1/4/2011)
sc-w (1/4/2011)
I nearly missed that post, i tried the following but it generates an error. Any thoughts?
update dbo.wce_contact set firstname = dbo.ProperCase(firstname)
Error:
Msg 1014, Level 15, State 1, Line 1
TOP clause contains an invalid value.
does the table dbo.wce_contact have a trigger on it? look at the body of the trigger...i'm guessing it is using the TOP command, which implies it might not be written as well as it could be.

Assuming it's not a trigger, it could also be a computed column that uses a function.

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

Ninja's_RGR'us (1/4/2011)
Lowell (1/4/2011)
sc-w (1/4/2011)
I nearly missed that post, i tried the following but it generates an error. Any thoughts?
update dbo.wce_contact set firstname = dbo.ProperCase(firstname)
Error:
Msg 1014, Level 15, State 1, Line 1
TOP clause contains an invalid value.
does the table dbo.wce_contact have a trigger on it? look at the body of the trigger...i'm guessing it is using the TOP command, which implies it might not be written as well as it could be.
Assuming it's not a trigger, it could also be a computed column that uses a function.

You will get that error if your table contains a NULL in firstname - the function does not handle that case. (see where it says

select top (len(@OriginalText)) row_number() over (order by N) as N from a4....)

It also does not handle zero length strings - it will return NULL for those.

MM

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

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

As you only have three small strings to make initial caps I would personally stick with your first thought....

UPDATE dbo.wce_contact

SET

firstname= STUFF (lower (firstname), 1, 1, UPPER (left (firstname, 1)))

, lastname = STUFF (lower (lastname), 1, 1, UPPER (left (lastname, 1)))

, contact = STUFF (lower (firstname), 1, 1, UPPER (left (firstname, 1)))

+ ' '

+ STUFF (lower (lastname), 1, 1, UPPER (left (lastname, 1)))

MM

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

sc-w Hall of Fame Points: 3664 More actions · Answer 10

That was just what i needed.

Thanks for all the help, one more quick thing. Those big functions what senario would you use those in?

Thanks again

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

sc-w (1/5/2011)
That was just what i needed.
Thanks for all the help, one more quick thing. Those big functions what senario would you use those in?
Thanks again

They are useful in any scenario, except that as they stand, they don't seem to work for your data, hence my offer of a tweak to your perfectly reasonable solution 😉

MM

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

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

I'm really surprised to see your good folks test results on the loop function for initial caps because I remember the original tests I did were quite conclusive in favor of that function. Guess I'll have to retest. Of course, I'll post my findings... Thanks for the feedback folks.

--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 13

mister.magoo (1/4/2011)
As you only have three small strings to make initial caps I would personally stick with your first thought....
UPDATE dbo.wce_contact
SET
firstname= STUFF (lower (firstname), 1, 1, UPPER (left (firstname, 1)))
, lastname = STUFF (lower (lastname), 1, 1, UPPER (left (lastname, 1)))
, contact = STUFF (lower (firstname), 1, 1, UPPER (left (firstname, 1)))
+ ' '
+ STUFF (lower (lastname), 1, 1, UPPER (left (lastname, 1)))

I absolutely agree with that notion. I suspect the McDonald family will not, though... especially Mary-Jane. 😀

--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

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

Jeff Moden (1/14/2011)
mister.magoo (1/4/2011)
As you only have three small strings to make initial caps I would personally stick with your first thought....
UPDATE dbo.wce_contact
SET
firstname= STUFF (lower (firstname), 1, 1, UPPER (left (firstname, 1)))
, lastname = STUFF (lower (lastname), 1, 1, UPPER (left (lastname, 1)))
, contact = STUFF (lower (firstname), 1, 1, UPPER (left (firstname, 1)))
+ ' '
+ STUFF (lower (lastname), 1, 1, UPPER (left (lastname, 1)))
I absolutely agree with that notion. I suspect the McDonald family will not, though... especially Mary-Jane. 😀

I can't imagine that Sean o'Flaherty will like it much either - but we can't please all the people all the time 😉

MM

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

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

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.
you can search for "ProperCase" or "InitCaps" here on SSC and find some other script contributions as well:
CREATE FUNCTION ProperCase(@OriginalText VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
DECLARE @CleanedText VARCHAR(8000)
;with
a1 as (select 1 as N union all select 1 union all
select 1 union all select 1 union all
select 1 union all select 1 union all
select 1 union all select 1 union all
select 1 union all select 1),
a2 as (select 1 as N from a1 as a cross join a1 as b),
a3 as (select 1 as N from a2 as a cross join a2 as b),
a4 as (select 1 as N from a3 as a cross join a2 as b),
Tally as (select top (len(@OriginalText)) row_number() over (order by N) as N from a4)
SELECT @CleanedText = ISNULL(@CleanedText,'') +
--first char is always capitalized?
CASE WHEN Tally.N = 1 THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))
WHEN SUBSTRING(@OriginalText,Tally.N -1,1) = ' ' THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))
ELSE LOWER(SUBSTRING(@OriginalText,Tally.N,1))
END
FROM Tally WHERE Tally.N <= LEN(@OriginalText)
RETURN @CleanedText
END
GO
select dbo.ProperCase('WHAT THE HECK IS GOIN ON AROUND HERE;')

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.

--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