Tip

Using INITCAP in 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

    Requires Free Membership to View

    Login 

 ' 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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top