SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 formatting to proper case
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 12/18/2005 :  22:52:51  Show Profile  Reply with Quote
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 - 12/18/2005 :  23:11:37  Show Profile  Reply with Quote
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

Australia
1479 Posts

Posted - 12/18/2005 :  23:23:43  Show Profile  Reply with Quote
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 - 12/18/2005 :  23:31:35  Show Profile  Reply with Quote
Thanks for trying to help me mate :)

Cheers
Go to Top of Page

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 12/18/2005 :  23:47:34  Show Profile  Reply with Quote
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"

Edited by - rrb on 12/18/2005 23:51:08
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 12/19/2005 :  02:01:15  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 - 12/19/2005 :  15:12:05  Show Profile  Reply with Quote
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

India
22744 Posts

Posted - 12/20/2005 :  00:18:11  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>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 - 12/20/2005 :  14:39:01  Show Profile  Reply with Quote
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

Edited by - rajani on 12/20/2005 14:39:27
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 12/20/2005 :  15:49:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 12/20/2005 :  16:22:02  Show Profile  Reply with Quote
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

Edited by - TG on 12/20/2005 16:28:24
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 12/20/2005 :  17:14:35  Show Profile  Reply with Quote
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

Australia
1479 Posts

Posted - 12/20/2005 :  18:13:12  Show Profile  Reply with Quote
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"

Edited by - rrb on 12/20/2005 18:21:20
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 12/20/2005 :  18:27:11  Show Profile  Reply with Quote
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

Australia
1479 Posts

Posted - 12/20/2005 :  19:27:10  Show Profile  Reply with Quote
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 - 12/20/2005 :  21:30:52  Show Profile  Reply with Quote
Thanks mate. You too :)

Cheers
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 12/20/2005 :  22:49:24  Show Profile  Reply with Quote
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

Australia
1479 Posts

Posted - 12/20/2005 :  23:17:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

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

jspc
Starting Member

Australia
1 Posts

Posted - 08/17/2009 :  18:30:35  Show Profile  Reply with Quote
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

India
22744 Posts

Posted - 08/18/2009 :  02:47:38  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000