1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43



IF EXISTS (SELECT * FROM sysobjects WHERE name = N'ufn_Proper')
	DROP FUNCTION ufn_Proper
GO

-- Function ufn_Proper
CREATE FUNCTION ufn_Proper
	(
		@Str varchar(8000),  
		@Delimiters varchar(12) = ' ' 
	) 
RETURNS varchar(8000)
-- @Str - source string to be converted to Proper format
-- @Delimiters - list of delimiters. Any letter after delimiter will be Upper cased. A space is default delimiter.
AS
BEGIN
	DECLARE @pos int, @CharIsDelimiter char(1)

	SET @CharIsDelimiter = 'Y'			-- 'Y' indicates that current character is a delimiter
	SET @Str 	= LOWER(@Str)			-- Convert whole string to lower case 
	SET @pos 	= 1						
	
	WHILE @pos <= LEN(@Str)
	BEGIN
		IF CHARINDEX(SUBSTRING(@Str, @pos, 1), @Delimiters) > 0
			-- The current character is a delimiter
			SET @CharIsDelimiter = 'Y'
		ELSE IF @CharIsDelimiter = 'Y'
		BEGIN 
			-- The current character is not a delimiter but previous character is
			--   Upper case the current character
			SET @Str = STUFF(@Str, @pos, 1, UPPER(SUBSTRING(@Str, @pos, 1)))
			SET @CharIsDelimiter = 'N' 
		END
		SET @pos = @pos + 1
	END	
	RETURN @Str
END
GO

-- Sample of using ufn_Proper UDF

SELECT dbo.ufn_Proper('The quick brown fox jumps over the lazy dog', DEFAULT)