Change first alphabet of a string to upper case

previoustoolboxuser · 2011-04-14T12:28:00.000Z

Hi I would like to change first letter to upper case of a data item type varchar (string) example I have a strings like( UpPer, DOWNTOWN, county) want to convert them to (Upper, Downton, County) just wanted the first letter to be in upper case and rest in lower case. Is there any function or substring code to do that in report studio. Thanks Shyera

spiceuser-njafomq7 · 2011-04-19T16:28:00.000Z

Guys,

I agree that it should be done at the DBA or ETL level…!!

I don’t think even the above steps will serve the purpose. If you see point 9. “When you have isolated the next word, create another calculated column that performs the upper and lower functions against it” ---- This works only when we have same data in the whole column.

Say, we have the data as below:

CamPing equipMent
MounTaineering eQuipement

Locating space in each word will be a hectic task, so I don’t think even the above steps would help.

So BI guys, lets push this activity to the DBA’s or ETL’s.

Thanks everyone & thanks Shyera for raising the issue…!!

Best,
Shrek

previoustoolboxuser · 2011-04-18T15:08:00.000Z

You’re right, Cognos’ solution is complex: Changing mixed case for a variable character string
Best to have the DBA’s sort it out in the data; using the mixed case in the report would probably result in a performance hit as well.

Changing mixed case for a variable character string

Technote (troubleshooting)

Problem(Abstract)
How can you change the mixed case for a variable character string?

Resolving the problem

Open the report that contains the column that you wish to apply mixed case or build a report with the desired column.
Build the calculation in steps to confirm what you are attempting to accomplish.
First calculation you will build is First Word: from the function folder select First Word and then from report columns, select the string column in question.
Create a calculate column called first length, which determines the length of this column via the function of CharLength.
Use the function Upper and Substring to capitalize the first letter. Then string together Lower and Substring for the rest of the word.
Example: upper(substring(first word,1,1)) + lower(substring(first word, 2, first length))
To create the second word, you would need to create a calculate column for it, first case;
Example: first-word(substring(

sachinkulkarni-8ibvkcua · 2011-04-18T11:04:00.000Z

Shrek,
InitCap function in Oracle does exactly what you are saying. I read somewhere that DB2 9.7 has this function implemented in it.
If you are interested in SQL Server, then there is no equivalent of it defined, yet. However, it can be implemented as a UDF. See this article for implementation of it -
http://www.sql-server-helper.com/functions/initcap.aspx
It is difficult to implement this logic using existing functions of Cognos and DB. You may be able to do it with a javascript of some sort, but then it will work only in HTML format!

barryabdul-xddjabe5 · 2011-04-16T16:25:00.000Z

Yes, Indeed, Terry is savior as he comes up quite intelligent solutions to complex scenarios. We truly owe you mate! Thanks

spiceuser-njafomq7 · 2011-04-16T14:08:00.000Z

Hi Terry,

Ya…I know it’s tough however I tried thinking you might come up with a surprising solution if any, which you usually do to most of the toughest situations…!!

Have a great weekend…!!

Best,
Shrek

previoustoolboxuser · 2011-04-16T12:28:00.000Z

Shrek,
I agree with Terry. Better not try this in Cognos. Regarding the lower function: Terry gave a simpler version of the construct we were discussing, but he forgot to add the ‘lower’ part on the right side. It still would give you only capitalization at the beginning of the string.

terencecurran-mj2q5ndy · 2011-04-15T19:36:00.000Z

What you are asking to do is beyond the capabilities of Cognos (or very nearly so) as you must iteratively move along a string, identifying the spaces and the words and changing the first letter to upper and the rest to lower, unfortunately there is no simple process to doing this! I would suggest you revert back to your ETL process and get that to do what you want

P Please consider the environment before printing this e-mail

previoustoolboxuser · 2011-04-15T15:33:00.000Z

Thank you all for the responses.

It worked.

spiceuser-njafomq7 · 2011-04-15T10:15:00.000Z

Hi Terry,
Below expression suggested by you is not working in the scenario explained below:
upper(left([String], 1)) + right([string], len([string]) - 1)
Scenario: Say, I have the following entries in my database:
trailChef Deluxe Cook Set
trailChef single Flame
When I apply the above expression, the output looks like below:
TrailChef Deluxe Cook Set
TrailChef single Flame
But, what I need is to have is Upper case letters at the start of each word & rest of the letters in each word in lower case like below:

Trailchef Deluxe Cook Set
Trailchef Single Flame

@ Gijs: I don’t think ‘Lower’ to the right string works for my above scenario…?

Any Suggestions…?

Best,
Shrek

spiceuser-j3ofdyxx · 2011-04-15T10:10:00.000Z

Per prior posts, string iteration is not a native operation in the standard
SQL string functions so while you can show techniques using
set operations which ‘fakes’ it the effort and scalability of the solution
is not ideal.

Vendor scalars or custom user defined functions are a better alternative and
if performed as a reporting-style expression in C++ better if you want
to avoid the load being applied in the database. Similarly, situation
dependent if this operation is applied over many tuples for many query
executions is that an ongoing cost you want to repeat vs amortizing it via
the source application or triggers etc which provides the form you want to
‘just’ filter or project against.

The other facet to consider is what is the business scenario … are you
trying to normalize user inputs in prompts to be applied in filters or
trying to marshall data for a formatting requirement and immediately trying
to solve it in SQL which may not be the approach place.

previoustoolboxuser · 2011-04-15T09:47:00.000Z

That looks good Terence, add lower to the right string to make it complete.
Shrek,
If you want to capitalize every word in a string, then you could use the Cognos position function, but that is going to be a challenge. I prefer a database function. Here are some interesting T-SQL ideas:

weblogs.sqlteam.com – 9 Mar 07

Simple T-SQL Proper Case User-Defined Function

I posted this one a long time ago and needed to use it today, so I thought I'd post it up here as well in case anyone finds it useful. This simply attempts to capitalize the first letter of each word for the string passed in.

terencecurran-mj2q5ndy · 2011-04-15T07:07:00.000Z

upper(left([String], 1)) + right([string], len([string]) - 1)

P Please consider the environment before printing this e-mail

This email is confidential and intended solely for the addressee[s].

For your security this email contains a digital signature which verifies the identity of the email sender.

If you are not the intended recipient you must not copy, distribute, disseminate, nor act upon the information. If you have received this message in error do not open any attachment, instead please inform the sender and then delete the message from your system.

We have taken reasonable steps to reduce risks against viruses but please rely on your own virus check as no responsibility or liability can be accepted for any damage sustained as a result of this transmission.

spiceuser-j3ofdyxx · 2011-04-14T23:16:00.000Z

The general portable solution could be as shown. Some replies may not
consider what happens for a 0 length
string ‘’ or a string of length ‘1’ and how their RDBMS if presented with
the expression may throw errors re subscripting into a non-existent character position.

case
when

spiceuser-njafomq7 · 2011-04-14T19:39:00.000Z

Hi Terry,

Can you tell us what we can write at COGNOS level if the backend is SQL Server to resolve the issue explained in my above post?

Best,
Shrek

terencecurran-mj2q5ndy · 2011-04-14T18:52:00.000Z

Some databases have a capitalize() function which does just that!

P Please consider the environment before printing this e-mail

spiceuser-njafomq7 · 2011-04-14T18:03:00.000Z

Winson & Gijs,

Thanks…Both the below worked…!!

substring(lower([gosales_goretailers].[Products].[ Product line]),2, char_length ([gosales_goretailers].[Products].[Product line]))

or

substring(lower([gosales_goretailers].[Products].[ Product line]),2, len ([gosales_goretailers].[Products].[Product line]))

My final expression is:

Left(upper([Sales (query)].[Product].[Product line]) ,1) + substring(lower([Sales (query)].[Product].[Product line]), 2, char_length([Sales (query)].[Product].[Product line]))

However, the above expression doesn’t work if we have two words, something like
‘Camping Equipment’. It displays ‘Camping equipment’ ( ‘e’ in the second word in lower case ).

Any solution…?

Best,
Shrek

previoustoolboxuser · 2011-04-14T17:31:00.000Z

Shrek,
You need to use

substring(lower(

previoustoolboxuser · 2011-04-14T17:30:00.000Z

Shrek,
I think, if you want to use Left, you should put it after the Upper() to make it work. I subtract 1 from the length because in the second part you start on position 2

This is an example from SQL Server Adventureworks database:
select UPPER(SUBSTRING(LastName,1,1))+ LOWER(SUBSTRING(LastName, 1, len(lastname)-1)) from Person.Contact

spiceuser-njafomq7 · 2011-04-14T17:14:00.000Z

Gijs…will the below code work for SQL Server??

Left(upper(