Author |
Topic |
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-12-18 : 22:52:51
|
Hi friendsI 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 helpCheers |
|
rajani
Constraint Violating Yak Guru
367 Posts |
|
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" |
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-12-18 : 23:31:35
|
Thanks for trying to help me mate :)Cheers |
|
|
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.CheersPS 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" |
|
|
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 functionsMadhivananFailing to plan is Planning to fail |
|
|
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 MadhivananYes,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
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 MadhivananFailing to plan is Planning to fail |
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-12-20 : 14:39:01
|
Madhivananin 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 |
|
|
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 MadhivananFailing 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
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' ) aorder by rowid Be One with the OptimizerTG |
|
|
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 |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2005-12-20 : 18:13:12
|
rajanitake 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" |
|
|
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 |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2005-12-20 : 19:27:10
|
if you need any help - post backMerry 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 - 2005-12-20 : 21:30:52
|
Thanks mate. You too :)Cheers |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-12-20 : 22:49:24
|
quote: Originally posted by rrb rajaniGet 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
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" |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-12-21 : 09:39:34
|
Hose myself? What is this, a Canadian forum? |
|
|
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 interestedhttp://vyaskn.tripod.com/code/propercase.txtCheers
|
|
|
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 interestedhttp://vyaskn.tripod.com/code/propercase.txtCheers
Create the function and runSELECT dbo.PROPERCASE(notes) FROM your_tableMadhivananFailing to plan is Planning to fail |
|
|
Previous Page&nsp;
Next Page
|