Scalar Functions

Listing 10-1 presents an example of a simple scalar function:

Listing 10-1. A basic scalar function.
CREATE FUNCTION dbo.Proper(@Name sysname)
RETURNS sysname
AS
BEGIN
  DECLARE @len int, @i int, @Outname sysname, @LastSpc bit
  SET @len=DATALENGTH(@Name)
  SET @i=1
  SET @LastSpc=1
  SET @Outname=''
  WHILE @i<@len BEGIN
    SET @Outname=@Outname+
     CASE @Lastspc
     WHEN 1 THEN UPPER(SUBSTRING(@Name,@i,1))
     ELSE LOWER(SUBSTRING(@Name,@i,1))
     END
    SET @LastSpc=CASE SUBSTRING(@Name,@i,1) WHEN ' ' THEN 1 ELSE 0 END
    SET @i=@i+1
  END
  RETURN(@Outname)
END
GO
SELECT dbo.Proper('thomas a. edison')

(Results)

---------------------------------------------------------------------------
Thomas A. Edison

The function in Listing 10-1 converts a string into “proper” ...

Get Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial