How to capitalize with SQL Upper or Lower the first letter of every word?

Other SQL Server Topics
1

I have a table with 181 names and they appear like this: ZEKELMAN INDUSTRIES.

I want them to look like this: Zekelman Industries.

I tried this code below but got this: Zekelman industries

SELECT Left(UPPER(account), 1)+Right(LOWER(account), len(account)-1)

I do not have the ability to do INITCAP or any functions. We are limited when it comes to functions.

2 
DROP TABLE IF EXISTS #;
CREATE TABLE #(name varchar(50) not null);
INSERT # VALUES('fred flintstone'),('BARNEy RUBBLe'),('george jetson')

SELECT STRING_AGG(CONCAT(UPPER(LEFT(Value,1)),LOWER(SUBSTRING(Value,2,1000))),' ')
FROM # A CROSS APPLY STRING_SPLIT(a.name,' ') z
GROUP BY a.name;
3

Just a sidebar here... unless you're using SQL Server 2022 with the "ordinal position" enabled, there is no guarantee that the data from STRING_SPLIT() will be returned in the proper order.

If you don't have SQL Server 2022 but you have at least SQL Server 2017, then the following will work (borrowing heavily from Robert Volk's good code)...

--===== Create a test table borrowing from Robert Volk
   DROP TABLE IF EXISTS #TestTable;
GO
 CREATE TABLE #TestTable(name varchar(50) not null);
 INSERT #TestTable VALUES('fred flintstone'),('BARNEy RUBBLe'),('george jetson'),('some full name')
;

--===== The pre-2022 solution.
 SELECT InitCaps = STRING_AGG(CONCAT(UPPER(LEFT(split.Item,1)),LOWER(SUBSTRING(split.Item,2,1000))),' ')
                   WITHIN GROUP ( ORDER BY split.ItemNumber) 
   FROM #TestTable tt
  CROSS APPLY dbo.DelimitedSplit8K(tt.name,' ') split
  GROUP BY tt.name
;

--===== For the 2022 solution.
 SELECT InitCaps = STRING_AGG(CONCAT(UPPER(LEFT(split.Value,1)),LOWER(SUBSTRING(split.Value,2,1000))),' ')
                   WITHIN GROUP (ORDER BY split.Ordinal) 
   FROM #TestTable tt 
  CROSS APPLY STRING_SPLIT(tt.name,' ',1) split
  GROUP BY tt.name
;

If you need it, you can get the DelimitedSplit8K function from the RESOURCE section at the end of the article located at...

Last but not least and although it still works even in SQL 2022, be advised that the use of "#" for Temp Tables and "##" for Global Temp Tables has been deprecated. Of course, they would be because they were simple and useful and, apparently, MS hates that. :yum:

4

I just checked - and I think you may have misunderstood the deprecated feature. What I read stated that using just a # or ## to identify a temp table is deprecated. The documentations states you must use at least 1 additional character.

1 Like
5

Notice the quotes that I included around "#" and "##". That's all that I was talking about as full table names. In other words, I agree with the inclusion of the word "just" and probably should have included that word to remove all doubt.

Given the context of the example of code on this thread, I did think it would be obvious, though.

1 Like