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
