0 Replies - 1519 Views - Last Post: 18 April 2008 - 05:16 AM

#1 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Proper case user defined function in MSSQL

Posted 18 April 2008 - 05:16 AM

Description: Pass the function the string you wish to work with.This is a user defined function for setting a string (or sentence) to it's proper case
CREATE FUNCTION dbo.udfProperCase(@value VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
	--variable to hold the return value
	DECLARE @final VARCHAR(8000)
	--set the initial string to lowercase
	SET @value = LOWER(@value) + ' '
	--set the final result variable to empty
	SET @final = ''

	--this ensures a loop
	WHILE 1=1
		BEGIN
			--if there are no spaces then exit the loop
	        	IF PATINDEX('% %',@value) = 0 BREAK
			--now we loop through each character and uppercase letters after a space
			--or at the beginning of the string
			SET @final = @final + UPPER(LEFT(@value,1)) + SUBSTRING(@value,2,CHARINDEX(' ',@value) -1)
			SET @value = SUBSTRING(@value,CHARINDEX(' ',@value)+1,LEN(@value))
		END
	SET @final = LEFT(@final,LEN(@final))
	--return the final string
	RETURN @final
END

--Sample Usage
SELECT dbo.udfProperCase('mAke tHIS PRopER CASE')


Is This A Good Question/Topic? 0
  • +

Page 1 of 1