question

sandeep_121 avatar image
sandeep_121 asked

To caps first letter of each word

Hi, could you please help me, i want to convert column's value in a specific order. currently i have a column(businessname). e.g. i have a value in that column like "KESSTECH IT SOLUTIONS GH LTD" then i want to change it in this case- "Kesstech It Solutions Gh Ltd" first letter caps of each word in the sentence.
sql-server-2008sql-server-2005sql-server-2012sql-server-2000sql server 2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thnaku so much sir, it's working.
0 Likes 0 ·
anthony.green avatar image
anthony.green answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.