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
WorknHardrAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Surendra NathConnect With a Mentor Technology LeadCommented:
the first method will still work, check out an example below
declare @t TABLE
(
LastNames VARCHAR(100)
)
INSERT INTO @T VALUES ('jack')
INSERT INTO @T VALUES ('jill')
INSERT INTO @T VALUES ('lincon')
INSERT INTO @T VALUES ('naruto')


SELECT STUFF(
(
SELECT ',' + Upper(Substring(LastNames, 1, 1)) + Lower(Substring(LastNames, 2,50)) 
FROM @T 
FOR XML PATH('')
),1,1,'')

Open in new window

0
 
WorknHardrAuthor Commented:
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
0
 
WorknHardrAuthor Commented:
thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.