PROPER (sentence Case) - Howto?

[JeffTullin]

I can't find a string function in TSQL to convert strings to Proper case.
(Found UPPER and LOWER)
In Access , I use STRCONV

Does anyone have a ready rolled function, or a sloution I have missed?

 

[hmckillop]

I dont know of any inbuilt string function which does this for you and
IMOH, the database isnt the best place for string manipulation, especially from a performance point of view.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[JeffTullin]

IMOH, the database isnt the best place for string manipulation, especially from a performance point of view."

Customer has a database where 24000 addresses have been loaded all in upper case, and wants them changing to Proper case.
How would you personally go about this?

 

[hmckillop]

lol!
You could create a user defined function. Here is a sample I have seen used on the internet

SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO

CREATE FUNCTION dbo.ufn_ChangetoProperCase (

    @tcString VARCHAR(100)
)   RETURNS VARCHAR(100)
AS BEGIN
-- Scratch variables used for processing
DECLARE @outputString VARCHAR(100)
DECLARE @stringLength INT
DECLARE @loopCounter INT
DECLARE @charAtPos VARCHAR(1)
DECLARE @wordStart INT

-- If the incoming string is NULL, return an error
IF (@tcString IS NULL)
	RETURN ('(no string passed)')

-- Initialize the scratch variables
SET @outputString = ''
SET @stringLength = LEN (@tcString)
SET @loopCounter = 1
SET @wordStart = 1

-- Loop over the string
WHILE (@loopCounter <= @stringLength)
BEGIN
	-- Get the single character off the string
	SET @charAtPos = LOWER(SUBSTRING (@tcString, @loopCounter, 1))

	-- If we are the start of a word, uppercase the character
	-- and reset the word indicator
	IF (@wordStart = 1)
	BEGIN
		SET @charAtPos = UPPER (@charAtPos)
		SET @wordStart = 0
	END

	-- If we encounter a white space, indicate that we
	-- are about to start a word
	IF (@charAtPos = ' ')
		SET @wordStart = 1

	-- Form the output string
	SET @outputString = @outputString + @charAtPos

	SET @loopCounter = @loopCounter + 1
END

-- Return the final output
RETURN (@outputString)
END
GO

--and to use it on a table called addresses

UPDATE Addresses
SET Field1 = ufn_ChangetoProperCase(field1)


"I'm living so far beyond my income that we may almost be said to be living apart

 

[Katy44]

But I think the point hmckillop was making is that it doesn't matter how a database actually stores data - when you retrieve it for the customer (e.g. in a report) THAT is the place to format!
Don't know if this helps, but it's just my opinion.