Wednesday, August 4, 2010

Function to convert a string in Camel Case (Proper Case)

Sometime, we need to display string data in proper case, i.e. first character of each word should be in UPPER case. SQL Server doesn't provide any built-in function for this requirement. In this article, I am putting T-SQL code to create this function. This is a generalised Function that will return any input string in Proper Case [Camel Case]. Means Afetr every space, first later will be in UPPER case.


Example:
Input String    :  'microSoft sql server'
Output String : 'Microsoft Sql Server'
 
/*************************************************
CREATED BY : HARI SHARMA ON MAR 30, 2007
PURPOSE : TO CONVERT INPUT STRING IN CAMEL CASE
**************************************************/
CREATE FUNCTION [dbo].[CamelCase]
(@Str varchar(8000))
RETURNS varchar(8000) AS
BEGIN
  DECLARE @Result varchar(2000)
  SET @Str = LOWER(@Str) + ' '
  SET @Result = ''
  WHILE 1=1
  BEGIN
    IF PATINDEX('% %',@Str) = 0 BREAK
    SET @Result = @Result + UPPER(Left(@Str,1))+
    SubString  (@Str,2,CharIndex(' ',@Str)-1)
    SET @Str = SubString(@Str,
      CharIndex(' ',@Str)+1,Len(@Str))
  END
  SET @Result = Left(@Result,Len(@Result))
  RETURN @Result
END
---------------------------------------------------
-- HOW TO USE --
-- SELECT dbo.CamelCase('hARi nARAyan shARMa')
-- Output: Hari Narayan Sharma
---------------------------------------------------
GO

1 comment:

  1. Thanks hari

    This is really useful. It solved my poblem.

    Awesome blog. Thanks a lot and keep it up.

    ReplyDelete

Here are few FREE resources you may find helpful.