Listing 10-1 presents an example of a simple 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.