|
|
For those database developers coming from Oracle and migrating to SQL Server for
whatever reason, it is sometimes frustrating to know that some of the string
functions, or any functions for that matter, that you have come accustomed with
in Oracle is not available in SQL Server. One of these string functions
is the InitCap
function.
For those who are not familiar with this Oracle string function,
InitCap is a string function that changes the first letter of a
string to uppercase. The remaining letters are made lowercase.
InitCap(<cl>) takes a single argument, where cl is a character
string. This function returns cl with the first character of each word in
uppercase and all others in lowercase.
Here's a user-defined function that will simulate the behavior of the Oracle
InitCap string function.
|
CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @Index = 1
WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
ELSE SUBSTRING(@InputString, @Index - 1, 1)
END
IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
BEGIN
IF @PrevChar != '''' OR UPPER(@Char) != 'S'
SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
END
SET @Index = @Index + 1
END
RETURN @OutputString
END
GO
Usage
Let's say you have a table containing book titles but the data as entered in the
table are all in uppercase and you want to set the titles capitalization
properly. Here's an example of your list of book titles:
SELECT [Title]
FROM [dbo].[Books]
Title
------------------------------------------------------------
A BUSINESS GUIDE TO CUSTOMER RELATIONSHIP MANAGEMENT
A PRACTICAL GUIDE TO CRM
ACCELERATING CUSTOMER RELATIONSHIPS
ACHIEVING EXCELLENCE THROUGH CUSTOMER MANAGEMENT
COMPLAINT MANAGEMENT
THE CUSTOMER MANAGEMENT SCORECARD
USING MICROSOFT CRM
Using the user-defined function above, the book titles above can look like the
following:
Title
------------------------------------------------------------
A Business Guide To Customer Relationship Management
A Practical Guide To Crm
Accelerating Customer Relationships
Achieving Excellence Through Customer Management
Complaint Management
The Customer Management Scorecard
Using Microsoft Crm
To produce this result, the query is as follows:
SELECT [dbo].[InitCap] ( [Title] ) AS [Title]
FROM [dbo].[Books]
As can be seen from the output, the first character in each word is converted to
uppercase while the rest of the characters of the word are all made to
lowercase. The same is the case for the word "CRM", as can be seen from
the book titles "A Practical Guide To Crm" and "Using Microsoft Crm".
Instead of maintaining it to all capital letters, it was changed to
"Crm". Since the user-defined function won't be able to know which words
need to be retained as all capital letters, these special cases have to be
handled manually.
|