capitalise the first letter of each word in a string

Question

I want to update table What’s the best way to capitalise the first letter of each word in a string in SQL Server ...Using Query Table Name :- MasterCategory Column Name:- Category

Midhun Tp · Answer

Hi, Please check below link for the function - http://stackoverflow.com/questions/55054/what-s-the-best-way-to-capitalise-the-first-letter-of-each-word-in-a-string-in-s First create the function in your database as in above link. Now use below query to update your table column - update MasterCategory set Category=dbo.InitCap(Category)

Ananth G · Answer

-- Drop the function if it already exists IF OBJECT_ID( 'dbo.InitCap' ) IS NOT NULL DROP FUNCTION dbo.InitCap; GO -- Implementing Oracle INITCAP function CREATE FUNCTION dbo.InitCap (@inStr VARCHAR (8000)) RETURNS VARCHAR (8000) AS BEGIN DECLARE @outStr VARCHAR (8000) = LOWER (@inStr), @ char CHAR (1), @alphanum BIT = 0, @len INT = LEN(@inStr), @pos INT = 1; -- Iterate through all characters in the input string WHILE @pos BEGIN -- Get the next character SET @ char = SUBSTRING (@inStr, @pos, 1); -- If the position is first, or the previous characater is not alphanumeric -- convert the current character to upper case IF @pos = 1 OR @alphanum = 0 SET @outStr = STUFF(@outStr, @pos, 1, UPPER (@ char )); SET @pos = @pos + 1; -- Define if the current character is non-alphanumeric IF ASCII(@ char ) OR (ASCII(@ char ) BETWEEN 58 AND 64) OR (ASCII(@ char ) BETWEEN 91 AND 96) OR (ASCII(@ char ) BETWEEN 123 AND 126) SET @alphanum = 0; ELSE SET @alphanum = 1; END RETURN @outStr; END GO Create above function then run below query select InitCap(s.Category) from MasterCategory s Sample Output: If answer is correct accept it

Ananth G · Answer

CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar (4000) ) RETURNS VARCHAR (4000) AS BEGIN DECLARE @ Index INT DECLARE @ Char CHAR (1) DECLARE @PrevChar CHAR (1) DECLARE @OutputString VARCHAR (255) SET @OutputString = LOWER (@InputString) SET @ Index = 1 WHILE @ Index BEGIN SET @ Char = SUBSTRING (@InputString, @ Index , 1) SET @PrevChar = CASE WHEN @ Index = 1 THEN ' ' ELSE SUBSTRING (@InputString, @ Index - 1, 1) END IF @PrevChar IN ( ' ' , ';' , ':' , '!' , '?' , ',' , '.' , '_' , '-' , '/' , '&' , '' '' , '(' ) BEGIN IF @PrevChar != '' '' OR UPPER (@ Char ) != 'S' SET @OutputString = STUFF(@OutputString, @ Index , 1, UPPER (@ Char )) END SET @ Index = @ Index + 1 END RETURN @OutputString END GO Use above code

Dr.Ajay Kashyap · Answer

Thks Ananth G sir and Midhun T P sir

Dr.Ajay Kashyap · Answer

Ananth sir not working

capitalise the first letter of each word in a string

Answers (5)