question

EDIT Added function to capitalize string. It's a 20 minute hack, so it definitely has room for improvement... END EDIT There are probably much more efficient ways to do it than his suggestion, but for a one-off I Think this will do. Unless your table contains millions of rows it should be reasoneably fast. Use CTEs where each CTE would do: - Split the string based on space as delimeter. Jeff Modens 8k-splitter which you find by searching on SqlServerCentral.com will do a fine job doing it and returning and index for each word so that you can put the string together again later. Include the key from the base table in this CTE - SELECT , the key from the base table, UPPER(LEFT(,1) + LOWER(RIGHT(,LEN(the Word)-1) FROM - SELECT DISTINCT , STUFF((SELECT ' ' + FROM ORDER BY WHERE = FOR XML PATH('')),1,1,'') FROM Something like that. And in the outer Query, you can run an update on the base table, using the last CTE in the FROM clause. An alternative solution would be to use C# to read each row and change it using a regular expression. The actual string operation would be much faster, but you would then have to do it Row By Row. Here's a capitalize-string function I hacked together just now: CREATE FUNCTION dbo.CapitalizeString(@s AS varchar(MAX)) RETURNS TABLE AS RETURN WITH CTE AS ( SELECT 1 AS n UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),CTE2 AS( SELECT ROW_NUMBER() OVER(ORDER BY c.n) AS n FROM cte AS c CROSS JOIN CTE AS c2 --100 CROSS JOIN CTE AS c3 --1000 CROSS JOIN CTE as c4 --10000 ), CTE_DelimeterPos AS( SELECT TOP(LEN(@s)+2) n AS pos FROM CTE2 WHERE SUBSTRING(' ' + @s + ' |',n,1)+'|' =' |' ), CTE_StartAndEnd AS( SELECT LAG(pos,1,1) OVER(ORDER BY pos)+1 AS StartPos, pos-LAG(pos,1,1) OVER(ORDER BY pos) AS lth FROM CTE_DelimeterPos WHERE pos<>1 ),Words AS ( SELECT LTRIM(RTRIM(SUBSTRING(' ' + @s+' ',StartPos,lth))) AS TheWord, ROW_NUMBER() OVER(ORDER BY startPos) AS idx FROM CTE_StartAndEnd )SELECT LTRIM(RTRIM(STUFF((SELECT ' ' + UPPER(LEFT(TheWord,1)) + LOWER(RIGHT(TheWord,LEN(TheWord)-1)) FROM Words ORDER BY idx FOR XML PATH('')),1,1,''))) AS TheSentence GO And here's an example of how it can be used: DECLARE @t TABLE(id INT IDENTITY(1,1),s VARCHAR(MAX)); INSERT @t (s) VALUES ('KESSTECH IT SOLUTIONS GH LTD'), ('TRANSMOKOPTER DATABASE SOLUTIONS'), ('microsoft corPORATION'); SELECT t.id,t.s AS OldString, c.TheSentence AS NewString FROM @t AS t CROSS APPLY dbo.CapitalizeString(s) AS c

You can also checkout the [SQLDownUnder][1] toolset, they have a range of functions which can do string manipulation like this. [1]: http://www.sqldownunder.com/Resources/SDUTools