| CREATE FUNCTION dbo.Capitalize ( -- Capitalize the first character of every word, -- sentence, or the whole string. Put the rest to lowercase. @String VARCHAR (8000), @Capitalize_What VARCHAR (8) = ’string’ -- String: Capitalize the first letter of the string -- Sentence: Capitalize the first letter of every sentence. -- Delimiters: ./!/? -- Word: Capitalize the first letter of every word. -- Delimiters: any characters other than letters and digits. ) RETURNS VARCHAR(8000) AS BEGIN DECLARE @Position SMALLINT, @Char CHAR(1), @First_Char CHAR (1), @Word_Start SMALLINT SET @Capitalize_What = LOWER( @Capitalize_What ) SET @Word_Start = 0 IF @Capitalize_What IN (‘word’, ‘sentence’) BEGIN SET @Position = DATALENGTH( @String ) WHILE @Position >= 0 BEGIN SET @Char = CASE @Position WHEN 0 THEN ’.’ ELSE UPPER( SUBSTRING( @String, @Position, 1 ) ) END IF @Char BETWEEN ’A’ AND ’Z’ OR @Char BETWEEN ’0’ and ’9’ BEGIN SET @Word_Start = @Position SET @First_Char = UPPER( @Char ) END ELSE BEGIN IF @Capitalize_What = ’word’ OR @Char in ( ’.’, ’!’, ’?’ ) BEGIN IF @Word_Start > 0 AND @First_Char BETWEEN ’A’ AND ’Z’ SET @String = STUFF( @String, @Word_Start, 1, @First_Char ) SET @Word_Start = 0 END END SET @Position = @Position - 1 END END ELSE BEGIN -- Capitalize the first character SET @Position = 0 WHILE @Position < DATALENGTH( @String ) BEGIN SET @Position = @Position + 1 SET @Char = UPPER( SUBSTRING( @String, @Position, 1 ) ) IF @Char BETWEEN ’A’ AND ’Z’ OR @Char BETWEEN ’0’ AND ’9’ BEGIN SET @String = STUFF( @String, @Position, 1, @Char ) SET @Position = 9999 END END END RETURN( @String ) END go |
正在阅读：UDF—SQL Server 2000的新特性UDF—SQL Server 2000的新特性
2004-04-01 13:25
出处：eNet学院
责任编辑：pjl
键盘也能翻页，试试“← →”键
