Oracle INITCAP equivalent function for SQL, C# and JavaScript

• Download source - 5.42 KB

Introduction

Oracle has an INITCAP function, which returns a string with the first letter of each word in uppercase and keep rest as non-capital. For example: the string 'hello world' would be returned as 'Hello World' and it's a really very nice and useful inbuilt function.

But unfortunately in SQL, C# and even JavaScript no such inbuilt function is available.

Background

In our application, in many places, we required the case when we needed to capitalize first letter of the word and keep the rest as non-capital.

But when I was Googling for a quick answer to this problem, I noticed that there are really no simple solutions listed for it in the web world.

That’s why I decided to build functions in SQL, C# and JavaScript. These functions are very easy to use and also work fine with strings that include numbers.

Using the Code

1. SQL Function

-- =============================================
-- Author: Samrat
-- Create date:  <Create Date, ,>
-- Description:    SQl function for capitalize first letter of the word and 
-- keep rest as non-capital 
=============================================
CREATE FUNCTION [dbo].[InitCapital] 
(@input_string varchar(1000))
 RETURNS varchar(1000)
AS
BEGIN
    DECLARE @alphabatList varchar(50)
SET @input_string=' '+lower(@input_string)

 DECLARE @cposition SMALLINT, @str VARCHAR(8000)
     SET @alphabatList  = 'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z'
     
    WHILE @alphabatList <> ''  
    BEGIN  
        SET @cposition = CHARINDEX(',', @alphabatList)  
        IF @cposition>0  
            BEGIN  
                SET @str = LEFT(@alphabatList, @cposition-1) 
                SET @alphabatList = RIGHT(@alphabatList, LEN(@alphabatList)-@cposition)  
                SET @input_string = replace(@input_string,' '+@str,' '+UPPER(@str))
            END  
        ELSE  
            BEGIN  
                SET @str = @alphabatList 
                SET @alphabatList = '' 
                SET @input_string = replace(@input_string,' '+@str,' '+UPPER(@str)) 
            END  
        
    END
    RETURN ltrim(@input_string) 

END

-- Ex. : SELECT dbo.InitCapital('hEllO wOrlD')

2. C# Function

Before move on to the below C# function, I would like to share about .NET inbuilt function which provides the same functionality.
Yes, we can also use the CultureInfo from Globalization and use the TextInfo’s ToTitleCase method in C# for the same purpose.
But one limitation with this method is that, this method does not currently provide proper casing to convert a word that is entirely uppercase, such as an acronym, i.e., if you are using System.Globalization.TextInfo.ToTitleCase(string s)then it works fine if you pass in an all lower or mixed case string. But if you are trying to make amends for data entry staff who like to leave Caps Lock on all the time, so all the input is in upper case.
ToTitleCase("HELLO WORLD") returns "HELLO WORLD" instead of "Hello World".

And we manage this limitation in the below function, in this function you don’t need to care about provided string format. This function internally manages this and provides proper desired result.

public string InitCapitalConvert(string input_string)
   {
       input_string = ' ' + input_string.ToLower();
         
       string[] AlphabatList  = new string[] {"a","b","c","d","e","f","g",
    "h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"};

       foreach (string alpha in AlphabatList )
       {
         input_string =  input_string.Replace(' '+alpha,' '+alpha.ToUpper());
       }
       return input_string;
   }

3. JavaScript Function

<script type="text/javascript">

   String.prototype.toINITCAP = function () {
       val = this;
       newVal = '';
       val = val.toLowerCase().split(' ');
       for (var c = 0; c < val.length; c++) {
           newVal += val[c].substring(0, 1).toUpperCase() +
             val[c].substring(1, val[c].length) + ' ';
       }
       return newVal;
   }

   function ShowInitCap() {
       var s = "HeLLo wOrld";
       var t = s.toINITCAP();
       alert(t);
   }

   ShowInitCap();

</script>

For more details, please refer to the uploaded code.

History

• 12^th November, 2011: Initial post