In-line functions in SQL Server 2000

Next time you are 'hard-coding' a common task, take a bit longer to write a function instead.

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.


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')
...will return '1000'.


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

For example:

SELECT dbo.fn_GetInitials ('Peter James Hammond')
...will return 'PJH'

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

 /** Returns value of a command line switch string **/
    @Switch         nvarchar(10),
    @StringValue    nvarchar(2048)
RETURNS nvarchar(2048)
    @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 ... **/

    @StringValue = @StringValue + ' '

(@Switch,@StringValue)),1,CHARINDEX(' ',SUBSTRING(@StringValue,CHARINDEX

RETURN @SwitchValue


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


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 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.

This was last published in June 2002

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.