SQL - Capitalize first letter of each word

Question

I know that this thread exists all over the place, however, this is a slightly different case. In the suite my company uses, I have limited access to the SQL and cannot run the complicated codes with functions, etc. I had a SQL query that compiled data over multiple columns into one column and used a group by clause to weed out multiplicities. However, this caused all the results to be returned in all caps, since variations existed. I must now change it back to how it should be, i.e. first letter of each word capitalized. I need a very concise way of doing this. The suite uses VBScript and XML, but this particular issue is more complicated because I cannot edit the results on the client-side, the suite simply asks for the column name to be displayed (populate a drop-down menu). Any suggestions? Thanks!

Query:

Select Insurance 
From
(Select Ins1 as Insurance
From InsAuth2
WHERE Ins1 IS NOT NULL
Union All
Select Ins2 as Insurance
From InsAuth2
WHERE Ins2 IS NOT NULL
Union All
Select Ins3 as Insurance
From InsAuth2
WHERE Ins3 IS NOT NULL
Union All
Select Ins4 as Insurance
From InsAuth2
WHERE Ins4 IS NOT NULL
Union All
Select Ins5 as Insurance
From InsAuth2
WHERE Ins5 IS NOT NULL) as table
Group By Insurance

look here: [SQL Server: Make all UPPER case to Proper Case/Title Case](http://stackoverflow.com/a/230224/277290) — YetAnotherUser, Jul 25 '12 at 15:42
You can't fix this on the client side and you can't use functions on the server side. Where do you think you can actually influence what is going on? If you think you can modify the query you have then please post what you have. Also what DBMS is this for? — Mikael Eriksson, Jul 25 '12 at 15:44
what redfilter suggested works, however, I need multi-word compatiblity — eatonphil, Jul 25 '12 at 15:46
yetanotheruser, I cannot get functions like that to work in this suite. — eatonphil, Jul 25 '12 at 15:49
I think a better question would be "why did your original Sql query cause your results to be in all caps?" I think fixing that problem would be a better solution than trying to capitalize the first letter of every word. — Michael Fredrickson, Jul 25 '12 at 15:49
What would happen if you remove `upper` from your field list? — Mikael Eriksson, Jul 25 '12 at 15:51
No, it had to. The group by clause sorted through results and variations on the results and returned only the distinct results with all caps to eliminate results. I.e. (Medicare, MEDICARE, MEDicare, etc.) Of course I only wanted the results that were in proper case, however some don't even come in proper case, some came in all caps. — eatonphil, Jul 25 '12 at 15:52
You don't have upper in your group by clause. Did you mean to have upper there as well? — Mikael Eriksson, Jul 25 '12 at 15:54
Sorry, Michael, that was an error on my part. The code that does this, operates and converts to upper case without the need of my inputting an upper command. — eatonphil, Jul 25 '12 at 15:55
The best working code is what RedFilter provided below. However, I need to be able to capitalize the first letter of every word. I can settle for what he provided but it is not the best solution to my issue. — eatonphil, Jul 25 '12 at 15:56

Mikael Eriksson · Accepted Answer · 2012-07-25T18:48:16.053

You did not say that this was for SQL Server but here is a solution in case it is.

Here is a working sample. Replace the table variable @T with the query you have.

declare @T table(Insurance varchar(max))

insert into @T values
('BENGT MIKAEL ERIKSSON'),
('XMLCHARTEST<>&''"')

select (
       select upper(T.N.value('.', 'char(1)'))+
                lower(stuff(T.N.value('.', 'varchar(max)'), 1, 1, ''))+' '
       from X.InsXML.nodes('/N') as T(N)
       for xml path(''), type
       ).value('.', 'varchar(max)') as Insurance
from 
  (
  select cast('<N>'+replace(
                    replace(
                    replace(Insurance,
                    '&', '&amp;'),
                    '<', '&lt;'),
                    ' ','</N><N>')+'</N>' as xml) as InsXML
  from @T
  ) as X

Result:

Insurance
----------------------
Bengt Mikael Eriksson 
Xmlchartest<>&'"

score 5 · Answer 2 · answered Jul 25 '12 at 15:42

5

This will uppercase the first letter of the string. If there are multiple words, this is better done in the application layer. You would likely need to write a SQL function to do this.

select upper(substring(MyColumn, 1, 1)) + substring(MyColumn, 2)
from MyTable

or

select upper(substring(MyColumn, 1, 1)) || substring(MyColumn, 2)
from MyTable

answered Jul 25 '12 at 15:42

D'Arcy Rittich

167,292
40
290
283

how about if it has multiple words? – eatonphil Jul 25 '12 at 15:43

SQL - Capitalize first letter of each word

2 Answers2

Linked