UDF—SQL Server 2000的新特性

核心提示： CREATE FUNCTION dbo.Capitalize ( -- Capitalize the first character of every word, -- sentence, or the whole string. Put the rest to lowercase. @String VARCHAR

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