Using INITCAP in Oracle and SQL Server

A well-annotated stored procedure showing how to use the Oracle funciton INITCAP in both Oracle and SQL Server.

I have always found the Oracle function "INITCAP" useful in SQL reports of names and addresses where the data processor

has used all upper or lowercase characters. This function makes the output more readable by initialising the first character of all words. This function is missing in Access BASIC. Here is a procedure for Oracle and SQL Server that will do the trick. It can be created in a module and called from a query using InitCap([fieldname]). Any word enclosed within parenthesis or quotes is left untouched, all others are converted into First letter uppercase and the rest lower.

First, the Oracle version:

 Function InitCap(StrString As Variant) As String ' Written by: Paul Webster ' Analog to Oracle INITCAP ' Non-Alphabetic characters are left unchanged ' Anything between double or single quotes is left unchanged ' Anything within brackets ({[]}) is left unchanged Dim StrNew, StrCurrent, StrPrevious As String Dim x As Integer x = 1 ' If the input string is empty, do now. If IsNull(StrString) Then StrNew = " " Else ' Prime previous character string StrPrevious = Left$(StrString, 1) ' Clear out new string StrNew = "" ' Begin loop Do While x < Len(StrString) + 1 ' Load current character StrCurrent = Mid$(StrString, x, 1) ' Are we at the start of the string? If x = 1 And StrCurrent <> " " Then ' If no leading spaces then make uppercase StrNew = StrNew + UCase$(StrCurrent) Else ' Test previous character Select Case StrPrevious ' Is is a space? Case Is = " " ' Yes! Is the current character also a space? If StrCurrent <> " " Then ' No then must be a first character StrNew = StrNew + UCase$(StrCurrent) End If ' Is is parenthesis or quotes? Case Is = Chr$(34), Chr$(39), Chr$(40), Chr$(91), Chr$(123) ' Yes so output as is StrNew = StrNew + StrCurrent ' Bump up counter x = x + 1 ' Get next character StrCurrent = Mid$(StrString, x, 1) ' Now loop until closing parenthesis is found Do While InStr(Chr$(34) + Chr$(39) + ")]}", StrCurrent) = 0 ' Outputting as is StrNew = StrNew + StrCurrent ' Bump up counter x = x + 1 ' Get next character StrCurrent = Mid$(StrString, x, 1) ' Until final parenthesis Loop ' Which we output as is StrNew = StrNew + StrCurrent Case Else ' OK so make it lowercase StrNew = StrNew + LCase$(StrCurrent) End Select End If ' Set previous character and StrPrevious = StrCurrent ' Bump up counter x = x + 1 ' Start again Loop End If ' Return modified string InitCap = StrNew End Function

This function is missing in SQL Server T-SQL. Here is a procedure that will do the trick. It is called using:

 EXECUTE Initcap @StrStr='String to be converted

Again, any word enclosed within parentheses or quotes is left untouched, all others are converted into first letter uppercase and the rest lower.

 /* Procedure: InitCap will capitalise all initial letters of a string passed in @StrStr Written by: Paul Webster April 2001 Usage: EXECUTE InitCap @StrStr='String to be capitalised' */ CREATE PROCEDURE [InitCap] @StrStr varchar(50) AS BEGIN DECLARE @StrNew varchar(50) DECLARE @StrCurrent varchar(1) DECLARE @StrPrevious varchar(1) DECLARE @x integer DECLARE @StrLen integer DECLARE @CloseBracket varchar(5) DECLARE @OpenBracket varchar(5) SELECT @StrPrevious = LEFT(@StrStr,1),@StrNew = ' ',@x = 1, @StrLen = LEN(@StrStr)+1 SELECT @OpenBracket = CHAR(34) + CHAR(39) + CHAR(40) + CHAR(91) + CHAR(123) SELECT @CloseBracket = CHAR(34) + CHAR(39) + CHAR(41) + CHAR(93) + CHAR(125) WHILE @x < @StrLen BEGIN SET @StrCurrent = SUBSTRING(@StrStr,@x,1) IF @x = 1 AND @StrCurrent <> ' ' SET @StrNew = @StrNew + UPPER(@StrCurrent) ELSE BEGIN IF (@StrPrevious = ' ' AND @StrCurrent <> ' ') SET @StrNew = @StrNew + UPPER(@StrCurrent) ELSE IF CHARINDEX(@StrPrevious,@OpenBracket) <> 0 BEGIN SET @StrNew = @StrNew + @StrCurrent SET @x = @x +1 SET @StrCurrent = SUBSTRING(@StrStr,@x,1) WHILE CHARINDEX(@StrCurrent,@CloseBracket) = 0 BEGIN SET @StrNew = @StrNew + @StrCurrent SET @x = @x +1 SET @StrCurrent = SUBSTRING(@StrStr,@x,1) END SET @StrNew = @StrNew + @StrCurrent END ELSE SET @StrNew = @StrNew + LOWER(@StrCurrent) END SET @StrPrevious = @StrCurrent SET @x = @x +1 END PRINT @StrNew END

 

For More Information


This was first published in October 2001

Dig deeper on Oracle stored procedures

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close