Capitalise values in my table - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Capitalise values in my table

Author

Topic

loydall
Starting Member

33 Posts

Posted - 2009-09-21 : 07:19:23

Hi - I have a table of US towns - they are all in lower case - new york, dallas, maryville etc.

I need them to be capitalised - New York, Dallas etc.. (only the first letter of each word needs to be Capitalised).

Is there a way to loop through my table and convert the first letter of each word to capitals?

Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-21 : 07:51:28

This will help you:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/09/60131.aspx

loydall
Starting Member

33 Posts

Posted - 2009-09-21 : 09:19:58

Thanks - that looks like what I'm after but I also need to know how to apply that function to every row of a table - how would I loop through each row and apply that function to a field?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-21 : 09:32:33

select
dbo.udfCapitaliseNamesFunction(<fieldtocapitalise>) as Capitalname
from <YourTable>

loydall
Starting Member

33 Posts

Posted - 2009-09-21 : 11:43:07

Thanks but that doesn't actually commit the change - that selects the values as Capitalised but it doesn't change the values in the source table - how do I commit this updates?

Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-21 : 11:50:38

Run it with an update then:

update <yourtable>
set <fieldtocapitalise> = dbo.udfCapitaliseNamesFunction(<fieldtocapitalise>)

I didn't think the step from select to update was that difficult?!?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-22 : 03:08:06

quote:
Originally posted by loydall

Thanks but that doesn't actually commit the change - that selects the values as Capitalised but it doesn't change the values in the source table - how do I commit this updates?

Thanks

You should better use it in SELECT statement instead of updating the table. What happens if new data are added to the table? You need to again do updation

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources