Link to home
Start Free TrialLog in
Avatar of WorknHardr
WorknHardr

asked on

SQL Uppercase First Char in Lastname using For XML Path?

This query never returns Cap (first char) when using 'For XML Path' in my example below.

Stuff ((SELECT Upper(Substring(ci.last_name, 1, 1)) + Lower(Substring(ci.last_name, 2,50))
	   FROM Contact_Info as ci
	   WHERE (ci.id = dbo.Contractors.id) FOR XML path('')), 1, 1, '') AS [LastNames]

Open in new window


Note: The same 'Upper/Lower' syntax work fine in this simple query:

SELECT Upper(Substring(ci.last_name, 1, 1)) + Lower(Substring(ci.last_name, 2,50))
FROM tblContact_Info as ci

Open in new window


Note: I've also used this function instead of the Upper syntax above, and it still returns ALL lowercase...

ALTER FUNCTION [dbo].[ProperCase]
(
  @string varchar(8000)
)
returns varchar(8000)
AS
   BEGIN
      set @string = lower(@string)
      declare @i int
      set @i = ascii('a')

while @i <= ascii('z')
begin

set @string = replace( @string, ' ' + char(@i), ' ' + char(@i-32))
set @i = @i + 1
end

set @string = char(ascii(left(@string, 1))-32) + right(@string, len(@string)-1)

return @string

End
go
grant execute on propercase to public
go
ASKER CERTIFIED SOLUTION
Avatar of Surendra Nath
Surendra Nath
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of WorknHardr
WorknHardr

ASKER

Wow, I was chopping-off the 1st char :)

Also works with a space like so: Stuff((SELECT ' ' + Upper(Substring.......

I'm trying to replace my Substring syntax for Stuff, seems a bit more friendly ...

Thanks
thx