Solved

SQL Uppercase First Char in Lastname using For XML Path?

Posted on 2014-02-12
3
314 Views
Last Modified: 2014-02-12
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
0
Comment
Question by:WorknHardr
  • 2
3 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39853207
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
 

Author Comment

by:WorknHardr
ID: 39853727
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
 

Author Closing Comment

by:WorknHardr
ID: 39853728
thx
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question