Recently I got the requirement of capitalizing the first letter of a string in a drop down list which holds some product related information.
The drop down list looks like the below screen:
From the above screen, I also got the requirement of replacing the letter ';' with the letter ':'. This string is coming from a SQL table and I got some business reasons for why I can't change this value during the insertion into the Database. As I couldn't control it during the insertion neither by writing a query for one time update, I had to follow a different approach. Then I only left with a choice of modifying the value during the selection. Then I wrote the below SQL function and used it in the SELECT statement.
Writing a SQL function:
The drop down list looks like the below screen:
Writing a SQL function:
CREATE FUNCTION [dbo].[CapitalizeFirstLetter] ( -- Add the parameters for the function here @InputStr VARCHAR(200) ) RETURNS VARCHAR(200) AS BEGIN --Declare a variable DECLARE @Index INT, @ResultStr VARCHAR(200) --Intialize the variables SET @Index = 1 SET @ResultStr = '' --Loop through the string for each letter WHILE (@Index < LEN(@InputStr) + 1) BEGIN IF (@Index = 1) --First letter of the string BEGIN --Make the first letter capital SET @ResultStr = @ResultStr + upper(substring(@InputStr, @Index, 1)) SET @Index = @Index + 1--increase the index END -- If the previous character is space or ';' ELSE IF ((SUBSTRING(@InputStr, @Index-1, 1) = ' ' OR SUBSTRING(@InputStr, @Index-1, 1) = ';') AND @Index+1 <> LEN(@InputStr)) BEGIN --make the letter capital SET @ResultStr = @ResultStr + UPPER(SUBSTRING(@InputStr,@Index, 1)) SET @Index = @Index + 1--increase the index END -- If the current character is ';' ELSE IF ((SUBSTRING(@InputStr, @Index, 1) = ';') AND @Index+1 <> LEN(@InputStr)) BEGIN --replace the character ';' with character ':' SET @ResultStr = @ResultStr + replace(SUBSTRING(@InputStr,@Index, 1),';', ':') SET @Index = @Index + 1--increase the index END ELSE --all others BEGIN --make the letter capital SET @ResultStr = @ResultStr + LOWER(SUBSTRING(@InputStr,@Index, 1)) SET @Index = @Index + 1--increase the index END IF (@@ERROR <> 0)-- If any error occurs, assign input string to the result string BEGIN SET @ResultStr = @InputStr END END RETURN @ResultStr END GOUsing the above function in a SELECT statement:
SELECT dbo.CapitalizeFirstLetter(Name) FROM tblProductResult: