Propercase? 2

[munchen]

Using TSQL I have a simple select statement(see below).

SELECT
tblA.EmpID,
tblA.Address
FROM
tblA

The address field has data that is all in uppercase like so:
10 NEW ROAD

However, I want the data to appear like so:
10 New Road

How is this possible to always capitalize the 1st letter after a space in TSQL?

 

[Jamfool]

you will need to write a function:

create function tcase(@str_in varchar(255))
returns varchar(255)

as
begin
declare @str_out varchar(255)
declare @spaces integer
declare @pos integer
declare @replace integer
declare @char varchar(1)


select @str_out = lower(@str_in)
select @spaces = 0
select @pos = 1
select @replace = 0
while @pos < Len(@str_out)
begin
select @char = substring(@str_out,@pos,1)
if @char=' '
select @replace=@pos+1
else
select @replace=0
if @pos=1
select @str_out = stuff(@str_out, 1, 1, upper(substring(@str_out, 1, 1)))
if @replace<>0
select @str_out = stuff(@str_out, @replace, 1, upper(substring(@str_out, @replace, 1)))
select @pos=@pos+1
end
return (@str_out)
end

 

[scorpio66]

I have another, slightly smaller and possibly quicker function for this:

CREATE FUNCTION dbo.FN_PROPER(@STR_IN NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
	DECLARE   @POS INT
		, @RETVAL NVARCHAR(4000)
		, @LEN INT

	SELECT @POS = CHARINDEX(' ', @STR_IN, 0), @RETVAL = UPPER(LEFT(@STR_IN, 1)) + LOWER(SUBSTRING(@STR_IN, 2, LEN(@STR_IN) - 1)), @LEN = LEN(@STR_IN)

	WHILE	@POS > 0
	BEGIN
		SELECT @RETVAL = LEFT(@RETVAL, @POS) + UPPER(SUBSTRING(@RETVAL, 1 + @POS, 1)) + RIGHT(@RETVAL, @LEN - @POS - 1)
		SELECT @POS = CHARINDEX(' ', @STR_IN, 1 + @POS)
	END

	RETURN @RETVAL
END

select dbo.FN_PROPER('10 NEW ROAD') returns

10 New Road

Chaz

 

[starbase59]

Thank you. Perfect.