Scalar Functions

for RuBoard

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" case. Each alphabetic character following a space is uppercased; the rest are lowercased. This is useful with many proper nouns, hence the name.

for RuBoard