Similar to Initcap in SQL server T-SQL

Question

Hi Experts,

I would like to convert first letter of the word as Upper and remaining in Lower.

select  [Name] from Table

now I had names like this JOHN BRAIN

                                         LIONA IAN O'CONNOR

So I wold like to see them as  John Brain

                                               Liona Ian O'Connor

How to do that could anyone guide me please

Thanks,

RK

Answer 1

It is a bit tricky and you may need to use a function. Take a look at this one

SQL Server Proper Case Function

We made some slight modifications in that function to cover complex cases like you list

create function [dbo].[Proper] (@cStringToProper varchar(8000))
returns varchar(8000)
as
begin
   declare  @Position int
   --** Adding next line to handle O', Mc and Mac names 
   select @cStringToProper = replace(replace(replace(replace(@cStringToProper, '-', '} '), '''', '| '), ' MC', '{ '), ' MAC', '* ')
   
   select @cStringToProper = stuff(lower(@cStringToProper) , 1 , 1 , upper(left(@cStringToProper , 1)))
        , @Position = patindex('%[^a-zA-Z][a-z]%' , @cStringToProper collate Latin1_General_Bin)
   
   while @Position > 0
         select @cStringToProper = stuff(@cStringToProper , @Position , 2 , upper(substring(@cStringToProper , @Position , 2)))
              , @Position = patindex('%[^a-zA-Z][a-z]%' , @cStringToProper collate Latin1_General_Bin)
   
   --** -- Adding next line to handle O', Mc and Mac names 
   select @cStringToProper = replace(replace(replace(replace(@cStringToProper, '* ', ' Mac'), '{ ', ' Mc'), '| ', ''''), '} ', '-')
   return @cStringToProper
end

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Thanks for your reply. I don't want to create the function. is that possible without using function...

I want to implement that in the view.

Thanks,

RK

Answer 3

SQL is really an inappropriate tool for such a transformation. If this is merely for presentation purposes, rather than updating the data as stored, then it it best done in the presentation layer where RegularExpressions analyzers are available. If this is part of a data-cleansing operation in loading a data warehouse, it is better done before the data is loaded into the DB, again where RegEx and such tools are available.

However, you will first have to spell out your requirments more precisely than "convert first letter of the word as Upper and remaining in Lower", as this example already violates the rule as provided:
    LIONA IAN O'CONNOR -> Liona Ian O'Connor
due to retaining capitalization of the 'C' following the apostrophe.

---

"Premature optimization is the root of all evil." - Knuth

If I provoked thought, please click the green arrow

If I provoked Aha! please click Propose as Answer

We are here to learn, to share knowledge, and to earn points; all in about equal measure.

Answer 4

I think it should be possible using recursive CTE also that may be put in a view. 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog