In-line functions in SQL Server 2000

One of the most under appreciated new features of SQL Server 2000 is the usage of functions. It is definitely worthwhile creating a "tool-kit" of commonly used functions to use in each new project. So next time you are hard-coding a common task, take a bit longer and write a function instead. Your fellow developers will thank you for it.

Below are a couple of scalar functions that can save lots of unnecessary temp table creating and cursor usage.

fn_GetSwitchValue

This function returns the value of a switch (i.e. -B{value}) out of a string given to it. Very handy for checking command line parameters.

Example usage:

SELECT dbo.fn_GetSwitchValue('-C','anycommand.exe -Swww499 -Nc:dirfile.txt -C1000')

fn_GetInitials

This function returns the initals of a string given to it.

For example:

SELECT dbo.fn_GetInitials ('Peter James Hammond')

Don't forget you can use these functions in any normal SELECT statement! For example:

 /** Returns value of a command line switch string **/
CREATE FUNCTION fn_GetSwitchValue
(
    @Switch         nvarchar(10),
    @StringValue    nvarchar(2048)
)
RETURNS nvarchar(2048)
AS
BEGIN
DECLARE
    @SwitchValue nvarchar(2048)

/** Logic of the substring requires the last character after a switch
    parameter MUST be a space ' ' there add one ... just in case ... **/

SELECT
    @StringValue = @StringValue + ' '

SELECT
    @SwitchValue = SUBSTRING(SUBSTRING(@StringValue,CHARINDEX
(@Switch,@StringValue)+2,DATALENGTH(@StringValue)-CHARINDEX
(@Switch,@StringValue)),1,CHARINDEX(' ',SUBSTRING(@StringValue,CHARINDEX
(@Switch,@StringValue)+2,DATALENGTH(@StringValue)-CHARINDEX
(@Switch,@StringValue))))

RETURN @SwitchValue

END
GO

/** Return initals of a string **/
CREATE FUNCTION fn_GetInitials 
 (@strName varchar(255))
RETURNS varchar(255)
AS
BEGIN
  DECLARE 
    @strInitials    varchar(255),
    @intStrPointer  int
  /** Trim off leading/trailing spaces **/
   
  SELECT @strName = LTRIM(RTRIM(@strName))
  SELECT 
    @intStrPointer = CHARINDEX(char(32),@strName),
    @strInitials = LEFT(@strName,1)
  WHILE @intStrPointer != 0
  BEGIN
    SELECT @strName = LTRIM(SUBSTRING(@strName,@intStrPointer,(DATALENGTH(@strName)-@intStrPointer)+1))
    SELECT 
      @intStrPointer = CHARINDEX(char(32),@strName),
      @strInitials = @strInitials + LEFT(@strName,1)
  END
  
  RETURN @strInitials
END


GO

Reader Feedback

The area of defined functions is indeed much ignored and little appreciated. However, this tip while implying the benefits failed to mention some of the very real downsides. Let's look at the function fn_GetInitials. First I have to assume that this is an often needed capability otherwise a function would not be created. If I am querying the Employee to grab the names and initials for all instances as in:

 
SELECT First_Name + ' ' + Middle_Name + ' ' + Last_Name                 AS Full_Name,
       Fn_GetIntials (First_Name + ' ' + Middle_Name + ' ' + Last_Name) AS Initials
  FROM Employee

then a awful lot of work has to be done by SQL Server for each record and if I have a large number of employees that results in a lot of work overall. Please note that I did not do any of the good testing for the existence of the name parts. If a full name and/or initials are needed then I would submit that a better method would be to use triggers on insert and update to populate full name and initial columns built from the name parts. The derivation of such values can get quite interesting due to the possible presence of prefixes (Dr., Senator, General, etc.) and suffixes (Jr., III, Sr., etc). Depending on the business needs, it certainly might be worth considering including three columns with derived values:

1. Full name w/last name first, such as Blake, Michael S. 
2. Full name w/last name last, such as Michael S. Blake 
3. Initials, such as MSB 

Of course this would increase storage costs but in many cases the improved performance by only deriving the values once would quickly offset those costs.

For More Information

• What do you think about this tip? E-mail the editor at tdichiara@techtarget.com with your feedback.
• The Best SQL Server Web Links: tips, tutorials, scripts, and more.
• Have an SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.