| Author |
 Topic  |
|
rajani
Constraint Violating Yak Guru
367 Posts |
 Posted - 12/18/2005 : 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 |
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
Posted - 12/18/2005 : 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" |
 |
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 12/18/2005 : 23:31:35
|
Thanks for trying to help me mate :)
Cheers |
|
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
Posted - 12/18/2005 : 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" |
Edited by - rrb on 12/18/2005 23:51:08 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 12/19/2005 : 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 |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 12/19/2005 : 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 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 12/20/2005 : 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 |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 12/20/2005 : 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 |
Edited by - rajani on 12/20/2005 14:39:27 |
|
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 12/20/2005 : 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. |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5500 Posts |
Posted - 12/20/2005 : 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 |
Edited by - TG on 12/20/2005 16:28:24 |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 12/20/2005 : 17:14:35
|
but there is no workaround for it , is it TG?
hopefully our clients wont complain much abt this :)
Cheers |
|
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
Posted - 12/20/2005 : 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" |
Edited by - rrb on 12/20/2005 18:21:20 |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 12/20/2005 : 18:27:11
|
Thanks for advice "rrb".
Thats what we'll be doing (i mean changing front end) atleast for new data.
Cheers |
|
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
Posted - 12/20/2005 : 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" |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 12/20/2005 : 21:30:52
|
Thanks mate. You too :)
Cheers |
|
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 12/20/2005 : 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? |
|
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
Posted - 12/20/2005 : 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" |
|
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 12/21/2005 : 09:39:34
|
| Hose myself? What is this, a Canadian forum? |
|
|
|
jspc
Starting Member
Australia
1 Posts |
Posted - 08/17/2009 : 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
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 08/18/2009 : 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 |
|
|
|
Topic |
|
formatting to proper case
