Pages

Wednesday, April 2, 2014

How to capitalize the first letter in sql ?

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:

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
GO


Using the above function in a SELECT statement:
SELECT dbo.CapitalizeFirstLetter(Name) FROM tblProduct

Result:


Thursday, March 20, 2014