SQL - Capitalize first letter of each word
sql
Solution
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,
'&', '&'),
'<', '<'),
' ','</N><N>')+'</N>' as xml) as InsXML
from @T
) as X
Result:
Insurance
----------------------
Bengt Mikael Eriksson
Xmlchartest<>&'"
Problem
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 ```