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