Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 formatting to proper case

Author  Topic 

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-12-18 : 22:52:51
Hi friends
I want convert a name field to proper case ,how can i do that thru a sql please ?? I mean covert "john disko" to "John Disko" etc.,

am using sql server 2005 standard edi.
Thanks for your help

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-12-18 : 23:11:37
Thanks all.
i found a script that works nicely.if anyone interested
http://vyaskn.tripod.com/code/propercase.txt

Cheers
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-18 : 23:23:43
sorry - you just beat me to it. I was just about to post exactly that script!

cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-12-18 : 23:31:35
Thanks for trying to help me mate :)

Cheers
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-18 : 23:47:34
no worries - btw, going with the UDF is probably the best way to go. there is no "inbuilt" function for this kind of string manipulation.

In my opinion however, it would be better to do this transformation before the data is stored, if possible, or when the data is rendered.

This is because the application of a function such as this may lead to an incorrect result - for example, I have a friend named "JJ Miller". But definitely not "Jj Miller". I would design the system in such a way that as soon as his name was entered, I would apply the function and update the new value back into the "user input form".

This gives the user the opportunity to check that the function has worked the way they would expect, and if not, to manually correct the result before it is stored in the database.

This approach also has the benefit that the application layer is probably best suited to code the function - but as I say, it may not be possible to "get at" that level.

Cheers

PS You may want to consider how to handle "John and Rajani Brown" too - just in case the data can be entered like that.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-19 : 02:01:15
Where do you want to show the data?
If you use Front Ends like VB6/.NET make use of Convert and ProperCase functions

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-12-19 : 15:12:05
Thank you very much rrb.Yes,you are correct it would be better to do this transformation before the data is stored .we've some legacy data where there's no such logic when inserting data but we would consider this the new app we are developing right now.

Hi Madhivanan
Yes,it is mostly for front end display (in a .Net app) we have different places where we need to do this so i thought if i could make a sql view (that calls relevant propercase function) everyone can simply use the sql view not worry abt formatting.

Cheers
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-20 : 00:18:11
>>not worry abt formatting.

Formatting issues should be dealt with Front Ends. As your front End is .NET I think you can very well use ProperCase(or equivalent) functions and dont give formatting work to SQL Server

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-12-20 : 14:39:01
Madhivanan
in our case ,our clients ,use report builder(sql server reporting services) to create reports so it is easy for them if we create a view which already does this formatting.

Cheers
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-12-20 : 15:49:29
quote:
Originally posted by madhivanan

>>not worry abt formatting.

Formatting issues should be dealt with Front Ends. As your front End is .NET I think you can very well use ProperCase(or equivalent) functions and dont give formatting work to SQL Server

Madhivanan

Failing to plan is Planning to fail


Agreed, but I don't think proper capitalization is a formatting issue. Proper capitalization is information itself, and data should be stored properly in the database. Else why not store all data in UPPER case?
Now, if an application wants to vary from standard formatting and display in all upper case or all lower case, then that is a true "formatting" issue and should be outside the scope of the database server.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-20 : 16:22:02
These types of formatting routines are quite difficult to get right 100% of the time. This one is no exception. I'm not saying don't use it just be aware of what it will miss.


select c [original]
,dbo.PROPERCASE(c) [propErcaSe]
from (
select 1 rowid, 'W.C. Fields' c union
select 2, 'FEMA blew it' union
select 3, '3Com' union
select 4, 'Ronald Mc''Donald' union
select 5, 'O''Reily Publishers' union
select 6, 'Thurston Howel III'
) a
order by rowid


Be One with the Optimizer
TG
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-12-20 : 17:14:35
but there is no workaround for it , is it TG?
hopefully our clients wont complain much abt this :)

Cheers
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-20 : 18:13:12
rajani

take the implicit advice (read between the lines) if its at all possible.

Get your data into the correct state by writing an application/script to cursor through each row and convert to proper case.

Once your data is in the correct state, modify your front-end application so all new data is stored correctly.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-12-20 : 18:27:11
Thanks for advice "rrb".
Thats what we'll be doing (i mean changing front end) atleast for new data.

Cheers
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-20 : 19:27:10
if you need any help - post back

Merry Christmas

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-12-20 : 21:30:52
Thanks mate. You too :)

Cheers
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-12-20 : 22:49:24
quote:
Originally posted by rrb

rajani
Get your data into the correct state by writing an application/script to cursor through each row and convert to proper case.

Cursor through? What is this, an Oracle forum?
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-20 : 23:17:56
alright blindman - hose yourself down...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-12-21 : 09:39:34
Hose myself? What is this, a Canadian forum?
Go to Top of Page

jspc
Starting Member

1 Post

Posted - 2009-08-17 : 18:30:35
Hello, are you able to please give me instructions on how to run this script? I tried to copy and paste in a Query window but did not run. Do I need to do something else?

I'm also wondering how do I select which table I want to run the script on?

You help wold be much appreicated.


quote:
Originally posted by rajani

Thanks all.
i found a script that works nicely.if anyone interested
http://vyaskn.tripod.com/code/propercase.txt

Cheers

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-18 : 02:47:38
quote:
Originally posted by jspc

Hello, are you able to please give me instructions on how to run this script? I tried to copy and paste in a Query window but did not run. Do I need to do something else?

I'm also wondering how do I select which table I want to run the script on?

You help wold be much appreicated.


quote:
Originally posted by rajani

Thanks all.
i found a script that works nicely.if anyone interested
http://vyaskn.tripod.com/code/propercase.txt

Cheers




Create the function and run

SELECT dbo.PROPERCASE(notes) FROM your_table

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
    Next Page

- Advertisement -