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.
Note: The same 'Upper/Lower' syntax work fine in this simple query:
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
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]
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thx
ASKER
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