Upper and Lower Case

[kbsc]

I'm trying to create a query that will display our database field with Upper and Lower case (currently the data is saved as all Upper case).

So for vchBusinessName = ARROW PLUMBING AND SERVICES, INC.

I want it to display = Arrow Plumbing And Services, Inc.

I've created this query:
SELECT (UPPER(substring(vchBusinessName, 1, 1)) + LOWER(substring (vchBusinessName, 2, 99)))
FROM tblDebtor
WHERE intCaseID = 442847 AND intDebtorType = 94

The output = Arrow plumbing and services, inc.

How do I account for when there's a space make that letter Upper Case and the rest Lower Case?? Please help, thanks in advance.

 

[ca8msm]

You are probably better of doing this on the front end rather than on the database.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[philhege]

BP Margolin posted this one.

 

[gmmastros]

A google search on [google]T-SQL Proper Case[/google] returns many results (many with code samples).

This one looks promising.

http://vyaskn.tripod.com/code/propercase.txt

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kbsc]

I've looked through the links everyone put up and the posts don't match what I need to do.

I am changing the value on the front end by using a merge field. But I have to write a select query that will change the data and then when the user pulls the report the data will display correctly.

Our merge fields work by creating the select query and then having it in our tblMergeFields and upload a word document with the merge field and it displays the data for the user.

 

[gmmastros]

Do this...

Open Query Analyzer.
Copy/Paste the contents of

http://vyaskn.tripod.com/code/propercase.txt

Run it by pressing F5 on your keyboard.

Open a new query window and copy/past this code...

SELECT dbo.ProperCase(vchBusinessName) As vchBusinessName
FROM tblDebtor
WHERE intCaseID = 442847 AND intDebtorType = 94

Run this by pressing F5 on your keyboard.

You may be pleasantly surprised.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ca8msm]

I've looked through the links everyone put up and the posts don't match what I need to do.

Rather than just looking, try testing them. The links you've been given should work exactly as you need them to.

I still think it may be less work for the front-end to do this though.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[kbsc]

Thanks for your help but the

http://vyaskn.tripod.com/code/propercase.txt

will not work for what I'm trying to do.

I can't use a function. I can only use a select query that will manipulate the data.

 

[ca8msm]

I can't use a function.

Why?

If you can't do this in SQL, maybe you should explore the route that I've mentioned twice previously.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]