Solved

SQL Uppercase First Char in Lastname using For XML Path?

Posted on 2014-02-12
3
304 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
Comment Utility
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
Comment Utility
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
Comment Utility
thx
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now